1.
Database tools and equipment
relational database management systems (RDBMS) like :
SQL, Oracle, or Microsoft SQL Server are commonly used
due to their ability to handle structured data and perform
complex queries.
Security is another critical aspect to consider when selecting
database tools and equipment.
Database systems like PostgreSQL and Microsoft SQL
Server provide advanced security features to protect your
data from unauthorized access or breaches.
Performance requirements play a significant role in determining
the appropriate database tools and equipment.
Tools like Redis or Apache Ignite.
enabling faster access and query execution
Scalability is also a crucial factor to consider, especially if your
project is expected to grow in terms of data volume or user base.
Tools like Apache Cassandra or MongoDB
SQL Server offers a comprehensive set of features and functionalities that
make it an ideal choice for learning advanced SQL.
1. Functions of where clause
Retrieving data from a table
The SQL WHERE clause is used to filter the results obtained by the DML statements
such as SELECT, UPDATE and DELETE etc.
It is used to extract only those records that fulfill a specified condition.
We can retrieve the data from a single table or multiple tables (after join operation) using
the WHERE clause.
We use SQL to retrieve the columns of a database table with the SELECT statement.
You can retrieve all columns, a single column, or several specific columns.
It is then up to your programming language to display that data.
Questions you may have include:
How do you retrieve all columns?
How do you retrieve a single column?
How do you retrieve some columns?
You can write a query to retrieve all the elements in a database table by using the
SELECT statement and wildcard (*) indicator.
Select * from table_name;
Note: It is standard practice to add ";" at the end of your SQL query.
The most common query from a database is to collect or retrieve all the elements in a
specific column of the database table.
Select column_name from table_name;
You can also retrieve data from several columns by separating the column names with a
comma.
Select column_1, column_2, column_7 from table_name;
Note: Do not place a comma after the last item in the list, because it will
result in an error message.
WHERE clause with SELECT statement
Typically, the SELECT statement is used to retrieve data from a table.
Example: Assume we have created a table named CUSTOMERS in SQL database
using CREATE TABLE statement and inserted some values.
The table created is as shown below.
ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
ID Name Salary
4 Fulea 6500
5 Kemal 8500
6 Momona 4500
7 Tibletse 10000
WHERE clause with UPDATE statement
The UPDATE statement is used to modify the existing records in a table.
Using the SQL WHERE clause with the UPDATE statement, Following is the syntax.
ID Name Age Address Salary
1 Tilahun 32 Gulele 3000
Comparison operators in the WHERE clause
SQL Comparison Operators test whether two given expressions are the same or not.
These operators are used in SQL conditional statements while comparing one
expression with another and they return a Boolean value which can be either TRUE
or FALSE.
Here is a list of all the comparison operators available in SQL.
Here is a list of all the comparison operators available in SQL.
Operator Description
= Equal to
!= Not equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
!< Not less than
!> Not greater than
Example: by using the previous customers table, we can write a query with a
comparison operator
Select * from customers where age != 25;
ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
3 Chemdesa 23 Lemikura 2000
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
Select * from customers where age >= 25;
ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables. They are used
to retrieve data from the database more quickly than otherwise. The users cannot
see the indexes, they are just used to speed up searches/queries.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX index_name
ON table_name (column1, column2, ...) ASC or DES;
1.Functions of order by clause
The SQL ORDER BY clause is used to sort the data in either ascending or
descending order, based on one or more columns.
This clause can sort data by a single column or by multiple columns.
ORDER BY is used with the SQL SELECT statement and is usually specified
after the WHERE, HAVING, and GROUP BY clauses.
Following are the important points about ORDER BY Clause
To sort the data in ascending order, we use the keyword ASC.
To sort the data in descending order, we use the keyword DESC.
Example: by using the previous product table
Sort the products from highest to lowest price
SELECT * FROM Products
ORDER BY Price DESC;
Sort the products from lowest to highest price
SELECT * FROM Products
Sort the products alphabetically by using Product name
SELECT * FROM Products
ORDER BY Product_Name;
Sort the products by Product Name in reverse order
SELECT * FROM Products
ORDER BY Product_Name DESC;
1. Boolean operators
Boolean operators in SQL are logical operators used to
combine or manipulate conditions in a query.
Operator Description
ALL TRUE if all of the sub query values meet the condition
AND TRUE if all the condition separated by AND is true
ANY TRUE if any of the sub query values meet the condition
BETWEE TRUE if the operand is within the range of comparisons
N
EXISTS TRUE if the sub query returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is true
SQL ALL Operator
The ALL operator:
Returns a Boolean value as a result
Returns TRUE if ALL of the sub query values meet the condition
It is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the
operation is true for all values in the range.
ALL operator syntax
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
SELECT * FROM Products
ORDER BY Price DESC;
Sort the products from lowest to highest price
SELECT * FROM Products
ORDER BY Price ASC;
Sort the products alphabetically by using Product name
SELECT * FROM Products
ORDER BY Product_Name;
Sort the products by Product Name in reverse order
SELECT * FROM Products
ORDER BY Product_Name DESC;
1. Boolean operators
Boolean operators in SQL are logical operators used to combine or
manipulate conditions in a query.
Operator Description
ALL TRUE if all of the sub query values meet the
condition
AND TRUE if all the condition separated by AND is true
ANY TRUE if any of the sub query values meet the
condition
BETWEE TRUE if the operand is within the range of
N comparisons
EXISTS TRUE if the sub query returns one or more records
IN TRUE if the operand is equal to one of a list of
expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is
true
Example: Assume we have created a table named CUSTOMERS in SQL
database using CREATE TABLE statement and inserted some values. The table
created is as shown below.
ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
SQL ALL Operator
The ALL operator:
Returns a Boolean value as a result
Returns TRUE if ALL of the sub query values meet the condition
It is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values
in the range.
ALL operator syntax
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
