Chapter 4

SQL
12/25/2024 Kahsay
Outline
• Structured Query Language
• Data types
• DDL, DML, TCL and DCL
• Basic Queries in SQL
• Nested Queries in SQL
• Views
• Comments
• Constraints

12/25/2024 Kahsay
SQL
• SQL, or Structured Query Language, is a specialized programming
language designed for:
• Managing and manipulating relational databases.
• It serves as a standardized means of interacting with databases,
allowing users to create, retrieve, update, and delete data.
• SQL provides a set of commands for tasks such as creating and
modifying database schemas, inserting and querying data, and
controlling access to the database.
• SQL operates on tables, which are organized into rows and columns,
forming a relational structure.
• SQL is widely used in DBMS like MySQL, PostgreSQL, Oracle, and
Microsoft SQL Server.
12/25/2024 Kahsay
Cont…
• We use SQL to create a database. SQL uses specific commands like
Create, Drop, Insert, etc., to carry out the required tasks.
• Generally the SQL commands are classified in to the following
categories.
• DDL – Data Definition Language
• DQL – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transaction control language

12/25/2024 Kahsay
DDL – Data Definition Language
• DDL consists of the SQL commands used to define the database schema.
• It simply deals with descriptions of the database schema and is used to create and
modify the structure of database objects in the database.
• These commands usually are not used by a general user, who should be accessing
the database via an application. Below are the commands used under this
category.
CREATE: is sql command to create tables and databases.
• Creating database: the syntax is given as follows
CREATE DATABASE DatabaseName;
• CREATE DATABASE is keyword and the name of the database should be unique as well as not
more than 128 characters
• E.g. CREATE DATABASE Student ; // Student database will be created/case insensitive
• SHOW DATABASE ; // to check its availabilities
• CREATE OR REPLACE DATABASE Employee; // to create or replace
database
• USE DatabaseName; // selecting database name
12/25/2024 Kahsay
CREATE TABLE
 Specifies a new base relation by giving it a name, and  Key attributes can be specified via the
specifying each of its attributes and their data types PRIMARY KEY and UNIQUE phrases
(INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), CREATE TABLE DEPT (
VARCHAR(n)) DNAME VARCHAR(10) NOT
 This command is used to create the database or its NULL,
objects (like table, index, function, views, store DNUMBER INTEGER NOT NULL,
procedure, and triggers) MGRSSN CHAR(9),
 A constraint NOT NULL may be specified on an MGRSTARTDATE CHAR(9),
attribute PRIMARY KEY (DNUMBER),
CREATE TABLE Persons (
PersonID int NOT NULL,
UNIQUE (DNAME),
LastName varchar(255), FOREIGN KEY (MGRSSN)
FirstName varchar(255), REFERENCES EMP);
Address varchar(255), Note: assume EMP table is
City varchar(255) available in the DB
);
12/25/2024 Kahsay
Cont…
• Specifying a primary key
CREATE TABLE users(
ID INT PRIMARY KEY AUTO_INCREMENT, username
VARCHAR(255) NOT NULL,
about TEXT, birthday DATE,
active BOOl );

12/25/2024 Kahsay
Cont…Create
• REFERENTIAL INTEGRITY OPTIONS
 We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on
referential integrity constraints (foreign keys)
CREATE TABLE DEPT (
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE);

12/25/2024 Kahsay
Cont…
• Inferential integrity cont…
• CREATE TABLE EMP(
ENAME VARCHAR(30) NOT NULL,
ESSN CHAR(9),
BDATE DATE,
DNO INTEGER DEFAULT 1,
SUPERSSN CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET
NULL ON UPDATE CASCADE);

12/25/2024 Kahsay
Drop
• This command is used to delete objects from the database.
• Used to remove a relation (base table) and its definition
• The relation can no longer be used in queries, updates, or any other
commands since its description no longer exists
• Example:
• Drop table TbaleName

DROP TABLE DEPENDENT;// dropping table

dependent
• DROP DATABASE Student; // dropping database student

12/25/2024 Kahsay
Alter Table
 Used to add an attribute to one of the base relations

The new attribute will have NULLs in all the tuples of the relation right after the
command is executed; hence, the NOT NULL constraint is not allowed for
such an attribute
• Example:
ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);//adding column
JOB
• ALTER TABLE table_name DROP Column column_name ; // Dropping column
• ALTER TABLE table_name RENAME COLUMN old_name to new_name; // Rename
• ALTER TABLE table_name MODIFY column_name column-definition;//Modify
• ALTER TABLE user2 RENAME TO user3//rename table

 The database users must still enter a value for the new attribute JOB for
each EMPLOYEE tuple.

This can be done using the UPDATE command.
12/25/2024 Kahsay
TRUNCATE TABLE
• A truncate SQL statement is used to remove all rows (complete data) from a
table but not the table itself.
• It is similar to the DELETE statement with no WHERE clause.
• TRUNCATE TABLE Vs DELETE TABLE
• Truncate table is faster and uses lesser resources than DELETE TABLE command.
• Drop Vs Truncate
• Drop table command can also be used to delete complete table but it deletes table
structure too. TRUNCATE TABLE doesn't delete the structure of the table.
• Syntax
• TRUNCATE TABLE table_name;
• TRUNCATE TABLE Employee;

12/25/2024 Kahsay
DQL: Data Query Language
• SELECT command
• The SELECT statement is the most commonly used command in SQL.
• It is used to access the records from one or more database tables and views.
• It also retrieves the selected data that follow the conditions we want.
• Select all columns
• SELECT * FROM tableName;// assume table name is ‘users’
• SELECT: First, we specify the action that we want to execute, in our
case, we want to select or get some data from the database.
• *: The star here indicates that we want to get all of the columns
associated with the table that we are selecting from.
• FROM: The from statement tells MySQL which table we want to select
the data from. You need to keep in mind that you can select from
multiple tables.
• users: This is the table name that we want to select the data from.
12/25/2024 Kahsay
Cont…
• SELECT specific columns only
• E.g. SELECT FName, active FROM users;// only two columns will be shown
• SELECT with Arithmetic Operations
• E.g. SELECT FName, salary*5 as new_active FROM users;

• General syntax
SELECT <attribute list>
FROM <table list>
WHERE <condition>

12/25/2024 Kahsay
E.g Database Schema

12/25/2024 Kahsay
Populated Data

12/25/2024 Kahsay
Example
• Example of a simple query on one  Query 3: For every project located in
relation 'Stafford', list the project number, the
• Query 1: Retrieve the birth date and controlling department number, and the
department manager's last name,
address of the employee whose name is
address, and birthdate.
'John B. Smith'.
 SELECT BDATE, ADDRESS FROM Q3: SELECT PNUMBER, DNUM,
EMPLOYEE WHERE FNAME='John' AND
MINT='B’ AND LNAME='Smith’; LNAME, BDATE, ADDRESS
FROM PROJECT,
 Query 2: Retrieve the name and DEPARTMENT, EMPLOYEE
address of all employees who work WHERE
for the 'Research' department. DNUM=DNUMBER AND
MGRSSN=SSN
 SELECT FNAME, LNAME, ADDRESS AND
FROM EMPLOYEE, DEPARTMENT PLOCATION='Stafford'
WHERE DNAME='Research' AND
DNUMBER=DNO;

12/25/2024 Kahsay
UNSPECIFIED WHERE-clause

• A missing WHERE-clause indicates no condition; hence, all tuples of the relations

in the FROM-clause are selected
• This is equivalent to the condition WHERE TRUE
• Query 4: Retrieve the SSN values for all employees.

• Q4: SELECT SSN FROM EMPLOYEE

12/25/2024 Kahsay
USE OF *

• To retrieve all the attribute values of the selected tuples, a * is used, which stands
for all the attributes
Examples:

Q1C: SELECT *
FROM EMPLOYEE
WHERE DNO=5

Q1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNO=DNUMBER

12/25/2024 Kahsay
DML- Data Manipulation Language
• INSERT
• To add data to your database, you would use the INSERT statement. You can
insert data into one table at a time only.
• There are two ways to insert data in a table:
• 1. SQL insert into statement
• By specifying column names
• INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• Without specifying column names
• INSERT INTO table_name VALUES (value1, value2, value3....);

• 2. Inserting data through SELECT Statement

INSERT INTO table_name [(column1, column2, .... column)] SELECT column
1, column2, .... Column N FROM table_name [WHERE condition];
Note: is it possible to insert multiple rows into a single table in a single
statement.
12/25/2024 Kahsay
Cont.…Insert
Inserting single line record Combining the multiple records
• INSERT INTO users (username, INSERT INTO users
email, active) VALUES ('user1', (username, email, active)
'[email protected]', true);
VALUES
• INSERT INTO users (username,
email, active) VALUES ('user1',
('user1', '[email protected]', true),
'[email protected]', true); ('user2', '[email protected]', true),
• INSERT INTO users (username, ('user3', '[email protected]', true),
email, active) VALUES ('user1', ('user4', '[email protected]', true),
'[email protected]', true);
• INSERT INTO users (username,
email, active) VALUES ('user1',
'[email protected]', true);
12/25/2024 Kahsay
Cont…
• UPDATE:
• The SQL commands (UPDATE and DELETE) are used to modify the data
that is already in the database. The SQL DELETE command uses a WHERE
clause.
• SQL UPDATE statement is used to change the data of the records held by
tables. Which rows is to be update, it is decided by a condition. To specify
condition, we use WHERE clause.
• The syntax is
UPDATE table_name SET [column_name1= value1,... column_nameN = val
ueN] [WHERE condition]
e.g. UPDATE students SET FirstName = ’alex' WHERE Student_Id = '3‘//single column
UPDATE students SET User_Name = 'beserious', First_Name = 'Johnny' WHERE Student_Id
= '3' // multiple columns
• UPDATE JOIN means we can update one table using another table and join condition.

12/25/2024 Kahsay
Cont…
• DELETE
• The SQL DELETE statement is used to delete rows from a table. Generally DELETE statement
removes one or more records from a table.
• Syntax
• DELETE FROM table_name [WHERE condition];
e.g. DELETE FROM EMPLOYEE WHERE ID=101;
e.g2. DELETE FROM EMPLOYEE; //will delete all the records
• WHERE clause is used to prevent the deletion of all the rows in the
table, If you don't use the WHERE clause you might loss all the rows.
• Note:
• There is a slight difference b/w delete and truncate statement. The DELETE statement only
deletes the rows from the table based on the condition defined by WHERE clause or delete all
the rows from the table when condition is not specified. But it does not free the space
containing by the table.
• The TRUNCATE statement: it is used to delete all the rows from the table and free the
containing space.
12/25/2024 Kahsay
USE OF DISTINCT

• SQL does not treat a relation as a set; duplicate tuples can appear
• To eliminate duplicate tuples in a query result, the keyword DISTINCT is used
• For example, the result of Q5 may have duplicate SALARY values whereas Q5.1
does not have any duplicate values

Q5: SELECT SALARY

FROM EMPLOYEE
Q5.1: SELECT DISTINCT SALARY
FROM EMPLOYEE

12/25/2024 Kahsay
NESTING OF QUERIES

• A complete SELECT query, called a nested query, can be specified within the
WHERE-clause of another query, called the outer query
• Many of the previous queries can be specified in an alternative form using nesting
• Query 1: Retrieve the name and address of all employees who work for the
'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' )

12/25/2024 Kahsay
NESTING OF QUERIES (contd.)

• The nested query selects the number of the 'Research' department

• The outer query select an EMPLOYEE tuple if its DNO value is in the result of
either nested query
• The comparison operator IN compares a value v with a set (or multi-set) of values
V, and evaluates to TRUE if v is one of the elements in V
• In general, we can have several levels of nested queries
• A reference to an unqualified attribute refers to the relation declared in the
innermost nested query
• In this example, the nested query is not correlated with the outer query

12/25/2024 Kahsay
CORRELATED NESTED QUERIES

• If a condition in the WHERE-clause of a nested query references an attribute of a relation declared

in the outer query, the two queries are said to be correlated
• The result of a correlated nested query is different for each tuple (or combination of tuples)
of the relation(s) the outer query
• Query 12: Retrieve the name of each employee who has a dependent with the same first name as
the employee.

Q12: SELECT E.FNAME, E.LNAME

FROM EMPLOYEE AS E
WHERE E.SSN IN
(SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME)

12/25/2024 Kahsay
CORRELATED NESTED QUERIES (contd.)

• In Q12, the nested query has a different result in the outer query
• A query written with nested SELECT... FROM... WHERE... blocks and using the = or
IN comparison operators can always be expressed as a single block query. For
example, Q12 may be written as in Q12A

Q12A: SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN AND
E.FNAME=D.DEPENDENT_NAME

12/25/2024 Kahsay
EXPLICIT SETS

• It is also possible to use an explicit (enumerated) set of values in the

WHERE-clause rather than a nested query
• Query 13: Retrieve the social security numbers of all employees who
work on project number 1, 2, or 3.
Q13: SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO IN (1, 2, 3)

12/25/2024 Kahsay
NULLS IN SQL QUERIES

• SQL allows queries that check if a value is NULL (missing or undefined or not
applicable)
• SQL uses IS or IS NOT to compare NULLs because it considers each NULL value
distinct from other NULL values, so equality comparison is not appropriate.
• Query 14: Retrieve the names of all employees who do not have supervisors.
Q14: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL
• Note: If a join condition is specified, tuples with NULL values for the join attributes
are not included in the result
• IS NOT Null for not null.

12/25/2024 Kahsay
Views, Comments , Constraints
• Views: Views in SQL are kind of virtual tables. A
view also has rows and columns as they are in a
real table in the database.
• We can create a view by selecting fields from one or
more tables present in the database.
• A View can either have all the rows of a table or specific
rows based on certain condition.
• We see about creating , deleting and updating Views.
CREATE VIEW view_name AS SELECT column1, column2.....
FROM table_name WHERE condition;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

12/25/2024 Kahsay
• Creating View from a single table
• In this example we will create a View named
DetailsView from the table StudentDetails.
Query:
CREATE VIEW DetailsView AS SELECT NAME, ADDRESS
FROM StudentDetails WHERE S_ID < 5;
Then, select views
SELECT * FROM DetailsView;

• Using order by
CREATE VIEW StudentNames AS SELECT S_ID, NAME
FROM StudentDetails ORDER BY NAME;

• Creating views from multiple tables

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

12/25/2024 Kahsay
Cont…
• Droping views
• DROP VIEW view_name
• Inserting into views
• INSERT INTO DetailsView(NAME, ADDRESS) VALUES(“Alex",“Axum");
• Deleting records from views
• DELETE FROM DetailsView WHERE NAME=“Alex";

12/25/2024 Kahsay
SQL comments
• Comments are used to explain sections of SQL statements, or to
prevent execution of SQL statements.
• Single Line Comments
• It start with
-- Select all:
SELECT * FROM Customers;
• Multi-line Comments
• Multi-line comments start with /* and end with */.
• /*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;
12/25/2024 Kahsay
Constraints
• SQL Constraints are used to limit the type of data that
can go into a table.
• This ensures the accuracy and reliability of the data in
the table.
• If there is any violation between the constraint and
the data action, the action is aborted.

12/25/2024 Kahsay
Cont…
• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between
tables
• CHECK - Ensures that the values in a column satisfies a specific
condition like, age>18
• DEFAULT - Sets a default value for a column if no value is specified

12/25/2024 Kahsay
Joining Commands
• In SQL, joining commands are used to combine rows from two or more
tables based on a related column between them.
• The most common types of joins are INNER JOIN, LEFT JOIN (or LEFT
OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or
FULL OUTER JOIN). Here's a brief explanation of each:

12/25/2024 Kahsay
• INNER JOIN: Returns only the rows that have
matching values in both tables.
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.column
= Table2.column;
E.G
SELECT * FROM employees INNER JOIN departments ON
employees.department_id = departments.id;

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the
left table (Table1), and the matched rows from the right table
(Table2). If there is no match, the result is NULL from the right
side.
SELECT * FROM Table1
LEFT JOIN Table2 ON Table1.column = Table2.column;
E.g.
SELECT * FROM employees LEFT JOIN departments ON
employees.department_id = departments.id;

12/25/2024 Kahsay
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns
all rows from the right table (Table2), and the
matched rows from the left table (Table1). If
there is no match, the result is NULL from the
left side.
SELECT * FROM Table1
LEFT JOIN Table2 ON Table1.column = Table2.column;
E.g.
SELECT * FROM employees Right JOIN departments ON
employees.department_id = departments.id;

• FULL JOIN (or FULL OUTER JOIN): Returns all rows when
there is a match in either left or right table. If there is no
match,
SELECT *NULL values are used for missing columns.
FROM Table1
FULL JOIN Table2 ON Table1.column = Table2.column;
E.g.
SELECT * FROM employees FULL JOIN departments ON
employees.department_id = departments.id;

12/25/2024 Kahsay
Aggregate Functions Commands
• In SQL, aggregation commands are used to
perform calculations on groups of rows to
return summary information. Some common
aggregation functions include:
• COUNT()
• AVG()
• SUM()
• MAX()
• MIN()

12/25/2024 Kahsay
• COUNT(): The COUNT command counts the number of
rows or non-null values in a specified column.
SELECT COUNT(column_name) FROM table_name;
E.G
SELECT COUNT(age) FROM employees; or * in place of age

• SUM(): The SUM command is used to calculate the sum of all values in a
specified column.
SELECT SUM(column_name) FROM table_name;
E.G
SELECT SUM(revenu) FROM sales;
• AVG(): The AVG command is used to calculate the average (mean) of all
values in a specified column.

SELECT AVG(column_name) FROM table_name;

E.G
SELECT AVG____ ) FROM products;

12/25/2024 Kahsay
• MIN(): The MIN command returns the minimum
(lowest) value in a specified column.
SELECT MIN(column_name) FROM table_name;
E.G
SELECT MIN(price) FROM Products;

• MAX(): The MAX command returns the maximum

(highest) value in a specified column.
SELECT MAX(column_name) FROM table_name;
E.G
SELECT MAX(price) FROM Products;

12/25/2024 Kahsay
SQL Querying Clause
• ORDER BY Clause
• The ORDER BY clause is used to sort the result set in ascending or descending
order based on a specified column.

SELECT * FROM table_name ORDER BY column_name

ASC|DESC; e.g
SELECT * FROM products ORDER BY price DESC;
• GROUP BY Clause
• The GROUP BY clause groups rows based on the values in a specified column. It is often
used with aggregate functions like COUNT, SUM, AVG, etc.

SELECT column_name, COUNT(*) FROM table_name

GROUP BY column_name;
e.g
SELECT category, COUNT(*) FROM products GROUP BY
category;

12/25/2024 Kahsay
Cont…
• HAVING clause
• The HAVING clause filters grouped results based on a specified condition.
• Specifies a condition to filter groups generated by the GROUP BY clause.

SELECT column_name, COUNT(*) FROM table_name

GROUP BY column_name HAVING condition;
e.g.
SELECT category, COUNT(*) FROM products GROUP BY
category HAVING COUNT(*) > 5;
-- Suppose we want to find customers who have spent more than $1000 in total.
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent FROM Orders
GROUP BY CustomerID HAVING SUM(TotalAmount) > 1000;

12/25/2024 Kahsay
String Functions in SQL
• CONCAT(): The CONCAT command concatenates two or more strings
into a single string.
• SELECT CONCAT (first_name,' ', last_name) AS full_name FROM employees;
• UPPER():
• The UPPER command converts all characters in a string to uppercase.
• SELECT UPPER (first_name) AS uppercase_first_name FROM
employees;
• LOWER():
• The LOWER command converts all characters in a string to
lowercase.
• SELECT LOWER(last_name) AS lowercase_last_name FROM
employees;
• REPLACE(): The REPLACE command replaces occurrences of a
substring within a string
• SELECT REPLACE (description,'old_string', 'new_string') AS
replaced_description FROM product_descriptions;
12/25/2024 Kahsay
Subqueries in SQL
• IN: The IN command is used to determine whether a value matches any
value in a subquery result. It is often used in the WHERE clause.
SELECT column(s) FROM table WHERE value IN (subquery); e.g.
SELECT * FROM customers WHERE city IN (SELECT city FROM suppliers);

• ANY: The ANY command is used to compare a value to any value

returned by a subquery. It can be used with comparison operators like =,
>, <, etc.
SELECT column(s) FROM table WHERE value < ANY (subquery);e.g.
SELECT * FROM products WHERE price < ANY (SELECT unit_price FROM
supplier_products);
• ALL: ALL command is used to compare a value to all values returned
by a subquery. It can be used with comparison operators like =, >, <, etc.
SELECT column(s) FROM table WHERE value > ALL (subquery); e.g.
SELECT * FROM orders WHERE order_amount > ALL (SELECT
total_amount FROM previous_orders);

12/25/2024 Kahsay
Set Operations
• UNION: The UNION operator combines the result sets of two or more
SELECT statements into a single result set
SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees;
• INTERSECT: The INTERSECT operator returns the common rows that
appear in both result sets.
SELECT first_name, last_name FROM customers
INTERSECT
SELECT first_name, last_name FROM employees;
• EXCEPT: The EXCEPT operator returns the distinct rows from the
left result set that are not present in the right result set.
SELECT first_name, last_name FROM customers
EXCEPT
SELECT first_name, last_name FROM employees;

12/25/2024 Kahsay
Other, clauses, operators
• The MySQL LIMIT Clause
• The LIMIT clause is used to specify the number of records to return.
• E.g, SELECT * FROM Customers LIMIT 3; //returns only 3 records
• The MySQL LIKE Operator
• The LIKE operator is used in a WHERE clause to search for a specified pattern
in a column. Two things here
• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character
• Eg. SELECT * FROM Customers WHERE CustomerName LIKE 'a%‘; //This SQL
statement selects all customers with a CustomerName starting with ‘a’ and
ending with any character/s

12/25/2024 Kahsay
• Eg2: SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; //This
SQL statement selects all customers with a CustomerName that have "or" in any
position.
• Eg3: SELECT * FROM Customers WHERE CustomerName LIKE 'a__%'; //
This SQL statement selects all customers with a CustomerName that
starts with "a" and are at least 3 characters in length(we have 2
underscores).
• Eg4: SELECT * FROM Customers WHERE
ContactName LIKE 'a%o'; //This SQL statement
selects all customers with a ContactName that
starts with "a" and ends with "o":
• MySQL NULL constraint
• A NULL value is different from a zero value or a field that contains spaces. A field with
a NULL value is one that has been left blank during record creation.
• We will have to use the IS NULL and IS NOT NULL operators.
• E.g.SELECT CustomerName, ContactName, Address
FROM Customers WHERE Address IS NULL; //This SQL lists all
customers with a NULL value in the "Address" field:
• SELECT CustomerName, ContactName, Address FROM Customers
WHERE Address IS NOT NULL; // This SQL lists all customers with a value in the
"Address" field

12/25/2024 Kahsay
The SQL SELECT TOP Clause
• The SELECT TOP clause is useful on large tables with thousands of
records. Returning a large number of records can impact
performance.
• E.g. SELECT TOP 3 * FROM Customers; // Select only the first 3 records of the
Customers table:
• SELECT TOP 3 * FROM Customers
WHERE Country='Germany';
• SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;// Sort the result reverse
alphabetically by CustomerName, and return the first 3 records:

12/25/2024 Kahsay
The SQL EXISTS Operator
• The EXISTS operator is used to test for the existence of any record in a
subquery.
• The EXISTS operator returns TRUE if the subquery returns one or more
records.
• SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Produc
ts.SupplierID = Suppliers.supplierID AND Price < 20);//The
following SQL statement returns TRUE and lists the suppliers with a
product price less than 20:
• Stored Procedure
• A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.
• So if you have an SQL query that you write over and over again, save
it as a stored procedure, and then just call it to execute it.
• CREATE PROCEDURE SelectAllCustomers AS
SELECT * FROM Customers
• To execute the stored procedure
• EXEC SelectAllCustomers;
12/25/2024 Kahsay