SQL – Part

2
Chapter 4
10/07/2025 1
Brainstorming
• Adding constraint using Alter to an
existing table
• ALTER TABLE Orders ADD CONSTRAINT fk_CustomerID
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID); // assume we have customer
table and want to create 1 to many rxnship with Orders
table.
• Adding column and constraint (foreign
key)
• ALTER TABLE Orders ADD COLUMN CustomerID INT, ADD
CONSTRAINT fk_CustomerID FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

10/07/2025 2
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

10/07/2025 3
• 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 [Link], [Link],
[Link]
FROM StudentDetails, StudentMarks
WHERE [Link] = [Link];

10/07/2025 4
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";

10/07/2025 5
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;

10/07/2025 6
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.

10/07/2025 7
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
• CREATE INDEX - Used to create and retrieve data from the
database very quickly.

10/07/2025 8
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:

10/07/2025 9
• INNER JOIN: Returns only the rows that have
matching values in both tables.
SELECT * FROM Table1 INNER JOIN Table2 ON [Link]
= [Link];
E.G
SELECT * FROM employees INNER JOIN departments ON
employees.department_id = [Link];

• 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 [Link] = [Link];
E.g.
SELECT * FROM employees LEFT JOIN departments ON
employees.department_id = [Link];

10/07/2025 10
• 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 [Link] = [Link];
E.g.
SELECT * FROM employees Right JOIN departments ON
employees.department_id = [Link];

• 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 [Link] = [Link];
E.g.
SELECT * FROM employees FULL JOIN departments ON
employees.department_id = [Link];

10/07/2025 11
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()

10/07/2025 12
• 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 SUM(price) FROM products;

10/07/2025 13
• 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;

10/07/2025 14
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;

10/07/2025 15
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;

10/07/2025 16
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;
10/07/2025 17
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);

10/07/2025 18
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;

10/07/2025 19
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
10/07/2025 20
• 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.
• [Link] 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

10/07/2025 21
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:

10/07/2025 22
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
[Link] = [Link] 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;
10/07/2025 23