Fetching and arranging data in Databases - ProgrammerTech
Language:

Search

Fetching and arranging data in Databases

  • Share this:
Fetching and arranging data in Databases

Basic SELECT clause in databases

A query ( SELECT )  in databases is a command, sentence, or code that fetches data from the database and displays it to the user. It may be a number or text ... etc. The SELECT query is used to fetch a copy of the data that was previously entered in the tables in order to display this data, whether in web pages or applications, or to keep a copy of it.

Syntax of the SELECT

SELECT * OR Columns [alias] from table
WHERE condition or conditions
[ORDER BY Column or Alias [ASC or DESC

whereas:

  • Columns: The name of the field or fields to be retrieved from the table.
  • Alises: Aliases for fields.
  • from: used to declare a condition or conditions.
  • Table: The name of the table to retrieve data from.
  • WHERE: Used to declare a condition or conditions.
  • Conditions: The condition or conditions necessary to limit the data that comes from the table.
  • ORDER BY: Declares how the data retrieved from the table will be ordered.
  • Column or Alies: The name of the table, fields, or aliases to be arranged with a semicolon to indicate the end of the command.

 

Important things when executing an SQL statement

  1. Put a semicolon (;) at the end of the sentence.
  2. Put a sign (/) at the end of the sentence at the <SQL index.
  3. To write the RUN command at <SQL index.

Example 1

1-73.webp
SQL>Select * from DEPT;

We display all the fields and data in the dept department table that contains. The following columns (DEPTNO, DNAME, LOC) using the symbol (*) mean that all fields are shown.

Example 2

View specific fields from the DEPT departmental table.

2-67.webp
SQL>Select deptno , dname from dept;

 

Aliases field views (computed fields)

  1. Use (AS) between alias and field name.
  2. Use the following double quotes (" ") between the alias and the field name.
  3. Use a space between the alias and the field name.

Example 3

Display aliased fields from the Employees table.

SQL> SELECT ename AS name , sal salary , job "employee job" 
    2	FROM emp;
3-62.webp

You can fetch data from more than one table at the same time

 

WHERE clause in databases

The WHERE conditional clause is used to enumerate rows based on a specific condition and also to arrange rows in ascending or descending order using the ORDER BY clause. The sentence WHERE is written after the FROM and is used to limit data on the basis of a certain condition or conditions. The condition is written from two sides, including the comparison operator, and when the condition is true, the SELECT clause has a result. If the condition is false, then the SELECT statement has no results.

 

WHERE clause components in databases

  1. field names columns.
  2. comparison operators.
  3. Fixed values, whether numeric or text.
  4. Arithmetic expressions.

 

Instructions for writing a WHERE clause for databases

1- When writing text values ​​or values ​​that represent a date, they must be written inside a quotation mark ( ' ' ).

2- If text values ​​are used, the case of uppercase or lowercase letters must be taken into account.

3- In the case of writing values ​​that express a date, the date format used must be taken into account, bearing in mind that the basic date format within SQL is (DD-MON-YY).

 

Sort data by ORDER BY in SQL

ORDER BY is used to arrange the resulting rows in ascending or descending order and is written at the end of the SELECT clause.

 

ORDER BY clause guidelines for ordering rows and columns

 

  1. It should be written at the end of the SELECT sentence.
  2. Contains field names for columns and aliases.
  3. Sort in ascending order We write the ASC function, which is an abbreviation of the word Asceding, and it is the default value of the default order.
  4. In descending order we write the function AESC, which is an abbreviation of the word Descending.

 

Comparison operators in the WHERE clause

1- modulus = equals.

2- The modulus < is greater than.

3- The parameter =< is greater than or equal to.

4- parameter > less than.

5- The parameter => less than or equal to.

6- Operator =! is not equal.

7- BETWEEN and AND operators limit data between two numbers.

8- The IN parameter limits the data to a set of values.

9- The LIKE parameter {%, _} limits data according to matching text or letters.

10- The parameter IS NULL counts the empty data.

Example 4

Displaying the names of the jobs and department numbers of the employees who work in the CLERK job, with the output arranged in ascending order according to the department number.

SQL> SELECT ename , job , deptno FROM emp WHERE job = 'CLERK' ORDER By deptno.
4-42.webp

Example 5

Display the names, positions and salaries of employees whose salaries are greater than or equal to 3000.

SQL> SELECT ename , job , sal  FROM emp WHERE sal<= comm
5-26.webp

We can combine more than one condition in WHERE using the logical operations NOT for negation, AND for all conditions, and OR for one of the conditions.


Wazeer Almuliky

Wazeer Almuliky

شاب يمني من صنعاء بكلريوس تقنية معلومات مهندس قطع إلكترونية و مبرمج تطبيقات اندرويد, خبرة في قواعد البيانات و الجافا, أهتم في الاكترونيات و لغات البرمجة منها فلاتر والجافا وسي شارب