Chapter 6: SQL Language (Part II)

DML (Data Manipulation Language)

The Data Manipulation Language (DML) is used to retrieve, insert and modify database
information

• Inserting Records (INSERT SQL Command)

• Updating Records (UPDATE SQL Command)
• Deleting Records (DELETE Command)
• The SELECT statement

Inserting Records (INSERT SQL Command)

INSERT INTO: This is used to add records into a relation. These are three type of INSERT
INTO queries which are as

a) Inserting a single record

Syntax:

INSERT INTO tablename (first_column,...,last_column)

VALUES (first_value,...,last_value)

Example
Run the following queries:

CREATE TABLE Student(

StudID varchar(15) primary key,
FirstName varchar(15),
LastName varchar(15),

1
 Sex varchar(6) default 'Male'
)
INSERT INTO Student (StudID, FirstName, LastName, Sex)
VALUES ('AB101', 'Jemal', 'Abdella', 'Male')

b) Inserting a single record

Syntax:
INSERT INTO tablename
VALUES (first_value,...,last_value)

INSERT INTO Student

VALUES ('AB102', 'Jemal', 'Abdella', 'Male')

INSERT INTO Student (Sex, FirstName, LastName, StudID)

VALUES ('Female', 'Jemal', 'Abdella', 'AB103'

c) SQL Server INSERT Multiple Rows

To add multiple rows to a table at once, you use the following form of the INSERT
statement.

INSERT INTO table_name (column_list)

VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

2
Exercise- INSERT Query
1. .Run the following create sql query in testdb

CREATE TABLE COMPANY(

ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
JOIN_DATE DATE
);
2. Run the following insert sql query

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE)

VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-
13');

The following example is to insert a row; here salary

column is omitted and therefore it will have the default
value –

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE)

VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

• The following example uses the DEFAULT clause for the

JOIN_DATE column rather than specifying a value

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE)

VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT)

3
 The following example inserts multiple rows using the
multirow VALUES syntax

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE)

VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13'
),(5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

UPDATE Statement

 To modify existing data in a table, you use the following UPDATE statement:

UPDATE
table_name
SET
c1 = v1,
c2 = v2,
...,
cn = vn
[WHERE condition]

In this syntax:

 First, specify the name of the table you want to update data after the UPDATE keyword.
 Second, specify a list of columns c1, c2, …, cn and new values v1, v2, … vn in the SET
clause.
 Third, filter the rows to update by specifying a condition in the WHERE clause. The
WHERE clause is optional. If you skip the WHERE clause, the statement will update all
rows in the table.

4
Examples:

1. Update a single column of one row of a table

UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

2. Update two columns of a row of a table

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000 WHERE ID

= 3;

3. Update all rows of a table

UPDATE COMPANY SET SALARY *=2; --Double the salary of all –

-employees

DELETE Statement
 To remove one or more rows from a table completely, you use the DELETE
statement. The following illustrates its syntax:

DELETE FROM table_name

WHERE [condition];

Examples

1. The following statement will delete all rows from the COMPANY table.
DELETE FROM COMPANY

2. The following statement will one row from the COMPANY table.

DELETE FROM COMPANY WHERE ID = 2

5
6
SELECT statement

The SELECT statement is used to query the database (one or more tables from the database) and
retrieve selected data that match the criteria that you specify. The SELECT statement has five main
clauses to choose from, although, FROM is the only required clause. Each of the clauses has a vast
selection of options, parameters, etc. The clauses will be listed and discussed below:

Here is the format of the SELECT statement:

USE database_name

SELECT [ALL | DISTINCT] column1 [, column2]

FROM table1 [, table2]

[WHERE "conditions"]

[GROUP BY "column-list"]

[HAVING "conditions]

[ORDER BY "column-list" [ASC | DESC] ]

ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or unique
records in your query results. If you would like to retrieve just the unique records in specified
columns, you can use the "DISTINCT" keyword. DISTINCT will discard the duplicate records for
the columns you specified after the "SELECT" statement.

7
Retrieving data from a single table

To retrieve data from a table, you use the SELECT statement with the following syntax:

SELECT
select_list
FROM
schema_name.table_name

Example:

1. Write a SELECT statement to retrieve the first and last names of all customers

SELECT FirstName,LastName
FROM customer;
Ouput:

The column names that follow the SELECT keyword determine which columns will be returned
in the results. You can select as many column names that you'd like, or you can use a "*" to
select all columns.

The table name that follows the keyword FROM specifies the table that will be queried to
retrieve the desired results.

The WHERE clause (optional) specifies which data values or rows will be returned or
displayed, based on the criteria described after the keyword WHERE. Comparison Operators
used in the WHERE clause are the following:

8
= Equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

<> or != Not equal to

LIKE String Comparison Test *See note below

*Note: LIKE is a very powerful operator that allows you to select only rows that are "like" what
you specify. The percent sign "%" can be used as a wild card to match any possible character
that might appear before or after the characters specified.

2. Write a SELECT statement to retrieve the FirstName,LastName and phone of all customers.
select FirstName,LastName,Phone
from customer

Using the SQL Server SELECT to retrieve all columns of a table

To retrieve data from all table columns, you can specify all the columns in the SELECT list.
Alternatively, you can also use SELECT * as a shorthand to select all columns:
select *
from customer

Output:

9
Exercises

1. From supplier table, list Id,companyName,Country and phone of each supplier.

2. Write a select query to retrieve all columns of the supplier table.
Note:

When processing the SELECT statement, SQL Server first processes the FROM clause, followed
by the SELECT clause, even though the SELECT clause appears before the FROM clause:

SQL Server SELECT TOP

The SELECT TOP clause allows you to limit the rows or percentage of rows returned by a
query. It is useful when you want to retrieve a specific number of rows from a large table.

Syntax:
SELECT TOP (expression) [PERCENT]
FROM
table_name

Example:

1. Write a query that returns Id,ProductName and UnitPrice of the 10 top records from products
table.

SELECT TOP (10) Id,ProductName,UnitPrice products

from PRODUCT
2. Using SELECT TOP to return a percentage of row

10
  Write a query that returns 5 percent of the whole records from the products table.

SELECT TOP 5 percent *

from [dbo].[Product]

Column Alias
A column alias allows you to assign a column or an expression in the select list of a SELECT
statement a temporary name. The column alias exists temporarily during the execution of the
query.
Syntax of using a column alias:
SELECT column_name AS alias_name FROM table_name
Or
SELECT column_name alias_name FROM table_name;
Note: The AS keyword is optional

Filtering rows using the WHERE clause

SQL WHERE Clause
The SQL WHERE clause is used to specify a condition while fetching the data from single
table or joining with multiple tables.

Syntax:
The basic syntax of SELECT statement with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition]

The WHERE clause (optional) specifies which data values or rows will be returned or
displayed, based on the criteria described after the keyword WHERE.

11
 o The SQL WHERE clause filters data that meet some criteria.
o WHERE only returns the rows you're interested in.
o A WHERE condition returns either true or false

Comparison Operators used in the WHERE clause are the following:

= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal to
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
IS NULL Return true if a value is NULL
NOT Negate the result of other operators

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE,
DELETE, etc.!

12
Order of Evaluation:
• Evaluates the WHERE clause after the FROM clause and before the SELECT and
ORDER BY clause:

Examples:
1. List all customers in Sweden.

SELECT Id, FirstName, LastName, City, Country, Phone

FROM Customer

13
 WHERE Country = 'Sweden'

2. Select all customers with a CustomerID greater than 80:

SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where Id>80

SQL AND and OR Operators

The SQL AND and OR operators are used to combine multiple conditions to narrow data in an
SQL statement. These two operators are called conjunctive operators.
These operators provide a means to make multiple comparisons with different operators in the
same SQL statement.

The AND Operator:

Syntax:
The basic syntax of AND operator with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

14
You can combine N number of conditions using AND operator. For an action to be taken by the
SQL statement, whether it be a transaction or query, all conditions separated by the AND must
be TRUE.

Example:
1. Select all customers with id greater than 80 and who are from ‘USA”
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where Id>80 and Country='USA'

The OR Operator:
The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
Syntax:
The basic syntax of OR operator with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN];

You can combine N number of conditions using OR operator. For an action to be taken by the
SQL statement, whether it be a transaction or query, only any ONE of the conditions separated
by the OR must be TRUE.

Example:
1. Select all customers with id greater than 80 or who are from ‘USA”
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where Id>80 or Country='USA'

15
Using WHERE clause with the IN operator
• If you want to match a string with any string in a list, you can use the IN operator.
• You use IN operator in the WHERE clause to check if a value matches any value in a list
of values.
• The syntax of the IN operator is as follows:
value IN (value1,value2,...)

Example:

1. The following statement returns customers whose first name is Howard, or John, or Mary:
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where FirstName in ('Howard', 'John','Mary')

16
Using WHERE clause with the NOT IN operator

You can combine the IN operator with the NOT operator to select rows whose values do not
match the values in the list.

Syntax:

value IN (value1,value2,...)

Example:

1. The following statement returns customers whose id is not 1,2,32,43,or 84

SELECT Id, FirstName, LastName, City, Country, Phone

FROM Customer
Where Id not in (1, 2, 32, 43, 84)

Using the WHERE clause with the LIKE operator

To find a string that matches a specified pattern, you use the LIKE operator.
There are two wildcards used in conjunction with the LIKE operator.

1. The percent sign (%)

2. The underscore (_)
The percent sign represents zero, one, or multiple characters. The underscore represents a single
number or character. The symbols can be used in combinations.

Syntax:

The basic syntax of % and _ is as follows:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

17
You can combine N number of conditions using AND or OR operators. Here, XXXX could be
any numeric or string value.
Statement Description
WHERE SALARY LIKE '200%' Finds any values that start with 200
WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position
WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third
positions
WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3
characters in length
WHERE SALARY LIKE '%2' Finds any values that end with 2
WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position
and end with a 3
WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2
and end with 3

Example:

1. List of customers whose names are starting with the letter ‘A’:
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where FirstName like 'A%'

2. List of customers whose names are starting with the letter ‘Ann’.
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where FirstName like 'Ann%'

Using the WHERE clause with the NOT LIKE operator

Example:

18
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where FirstName not like 'Ann%'

Using the WHERE clause with the BETWEEN operator

The following example finds customers whose customer_id is between 1 and 10

SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
Where Id between 1 and 10

Using the WHERE clause with the NOT BETWEEN operator

SELECT Id, FirstName, LastName, City, Country, Phone

FROM Customer
Where Id not between 1 and 10

Using the WHERE clause with the not equal operator (<>)

Example:

Finds customers whose first names start with Yoshi and last names are not Latimer.

SELECT Id, FirstName, LastName, City, Country, Phone

FROM Customer
where FirstName ='Yoshi' and LastName<>'Latimer'

• Note that you can use the != operator and <> operator interchangeably because they are
equivalent.

19
Using the WHERE clause with the IS NULL operator

 A null value is a missing entry in a column.

 Null means ‘unknown’ or ‘does not apply’.
 Nulls are neither blanks nor zeros (two null are not necessarily equal, and you cannot
do arithmetic with nulls).The IS NULL operator locates rows with null values.

Example:

Select FirstName,LastName,Phone
From Customer
Where Phone IS NULL

Table aliases

• Table aliases temporarily assign tables new names during the execution of a query.

• The following illustrates the syntax of a table alias:

table_name AS alias_name;

or

table_name alias_name;

SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on
one or more columns. Some database sorts query results in ascending order by default.

Syntax:
The basic syntax of ORDER BY clause is as follows:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1 [ASC | DESC], column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Make sure whatever column you
are using to sort, that column should be in column-list.

20
Order of Evaluation

Example:
1. The following query uses the ORDER BY clause to sort customers by their first names in
ascending order:
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
order by FirstName
2. The following query uses the ORDER BY clause to sort customers by their first names in
descending order:
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
order by FirstName desc

3. ORDER BY clause to sort rows by multiple columns

The following statement selects the first name and last name from the customer table and
sorts the rows by the first name in ascending order and last name in descending order.
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
order by FirstName asc,LastName desc

SELECT DISTINCT clause

• The DISTINCT clause is used in the SELECT statement to remove duplicate rows from a
result set.
• The DISTINCT clause keeps one row for each group of duplicates.
• The DISTINCT clause can be applied to one or more columns in the select list of the
SELECT statement.

SELECT SELECT

DISTINCT column1 DISTINCT column1,column2

FROM FROM

table_name; table_name;

21
CREATE TABLE distinct_demo (
id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
fcolor VARCHAR
);

INSERT INTO distinct_demo (bcolor, fcolor)

VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');

1.
SELECT
id,
bcolor,
fcolor
FROM distinct_demo ;

2.
SELECT DISTINCT bcolor
FROM distinct_demo
ORDER BY bcolor;

3. DISTINCT multiple columns

SELECT DISTINCT bcolor, fcolor

FROM distinct_demo
ORDER BY bcolor, fcolor;

• The query returns the unique combination of bcolor and fcolor from the distinct_demo
table

22
Aggregate functions
• An aggregate function is a function that performs a calculation on a set of values, and returns
a single value.
• Are used to summarize the results of a particular column
• Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
The GROUP BY clause splits the result-set into groups of values and the aggregate function
can be used to return a single value for each group.
• These are:
– MIN():returns the smallest value within the selected column
– MAX():returns the largest value within the selected column
– SUM():returns the total sum of a numerical column
– AVG():returns the average value of a numerical column
– COUNT():returns the number of rows in a set

COUNT Function

SQL COUNT function is the simplest function and very useful in counting the number of
records, which are expected to be returned by a SELECT statement.

Example:
1. Count total number of rows in the Customer’s table, then you can do it as follows:

SELECT COUNT(*) FROM customer ;

2. Use column alias to rename the column for the count result:

SELECT count(*) [Number of Rows]

FROM Customer

3. Get the total number of products.

SELECT COUNT(Id) AS 'Product Count'

FROM Product

SQL MAX Function

SQL MAX function is used to find out the record with maximum value among a record set.
Example:

23
1. Find the largest order amount.

SELECT max(TotalAmount) as 'Largest Amount'

FROM Order

SQL SUM Function

SQL SUM function is used to find out the sum of a field in various records.

Example: Calculate the total sales in 2013.

SELECT SUM(TotalAmount) AS 'Total Sales'

FROM [Order]
WHERE YEAR(OrderDate) = 2013

SQL AVG Function

SQL AVG function is used to find out the average of a field in various records.\
Example:
Problem: Calculate the average size of all orders.
SELECT AVG(TotalAmount) AS 'Avg Order'
FROM [Order]

SQL GROUP BY
The GROUP BY clause will gather all of the rows together that contain data in the specified
column(s) and will allow aggregate functions to be performed on the one or more columns.

 The GROUP BY clause groups records into summary rows.

 GROUP BY returns one record for each group.
 GROUP BY is used with aggregrates: COUNT, MAX, SUM, etc.

GROUP BY syntax with ORDER BY.

SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
ORDER BY column-names

24
Example:
List the number of customers in each country.

SELECT Country, COUNT(Id) AS Customers

FROM Customer
GROUP BY Country

2. List the number of customers in each country, sorted high to low.

SELECT Country, COUNT(Id) AS Customers

FROM Customer
GROUP BY Country
ORDER BY COUNT(Id) DESC
25
SQL HAVING

 HAVING is like WHERE but operates on grouped records.

 HAVING requires that a GROUP BY clause is present.
 Groups that meet the HAVING criteria will be returned.
 HAVING is used with aggregrates: COUNT, MAX, SUM, etc.

Table Joins
All of the queries up until this point have been useful with the exception of one major limitation -
that is, you've been selecting from only one table at a time with your SELECT statement. It is time
to introduce you to one of the most beneficial features of SQL & relational database systems - the
"Join". To put it simply, the "Join" makes relational database systems "relational".

Joins allow you to link data from two or more tables together into a single query result--from one
single SELECT statement.

A "Join" can be recognized in a SQL SELECT statement if it has more than one table after the
FROM keyword.

Syntax:

SELECT list-of-columns

FROM table1, table2

WHERE search-condition(s)

26
The following queries are based the schema shown below.

-- Create the database

CREATE DATABASE JoinExamples;

GO

USE JoinExamples;

GO

-- Create Departments table

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY IDENTITY(1,1),

DepartmentName VARCHAR(50) NOT NULL,

Location VARCHAR(50)

);

27
-- Create Employees table

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY IDENTITY(1,1),

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,

Email VARCHAR(100),

HireDate DATE,

Salary DECIMAL(10,2),

DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)

);

-- Create Projects table

CREATE TABLE Projects (

ProjectID INT PRIMARY KEY IDENTITY(1,1),

ProjectName VARCHAR(100) NOT NULL,

Budget DECIMAL(12,2),

StartDate DATE,

EndDate DATE

);

28
 -- Create junction table for many-to-many relationship

CREATE TABLE EmployeeProjects (

EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID),

ProjectID INT FOREIGN KEY REFERENCES Projects(ProjectID),

HoursWorked INT,

PRIMARY KEY (EmployeeID, ProjectID)

);

-- Insert sample data into Departments

INSERT INTO Departments (DepartmentName, Location)
VALUES
('IT', 'Floor 1'),
('HR', 'Floor 2'),
('Finance', 'Floor 3'),
('Marketing', 'Floor 4'),
('Operations', 'Floor 5'),
('Management','FLoor 6'),
('Personnel','FLoor 8');

-- Insert sample data into Employees

INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES
('John', 'Smith', '[Link]@[Link]', '2020-01-15', 75000, 1),
('Sarah', 'Johnson', '[Link]@[Link]', '2019-05-22', 82000, 2),
('Michael', 'Williams', '[Link]@[Link]', '2021-03-10', 68000, 1),
('Emily', 'Brown', '[Link]@[Link]', '2018-11-05', 90000, 3),
('David', 'Jones', '[Link]@[Link]', '2022-02-18', 72000, 4),
('Jessica', 'Garcia', '[Link]@[Link]', '2020-07-30', 78000, 2),
('Daniel', 'Miller', '[Link]@[Link]', '2021-09-12', 85000, NULL),
('Lisa', 'Davis', '[Link]@[Link]', '2019-04-25', 95000, 5);

-- Insert sample data into Projects

INSERT INTO Projects (ProjectName, Budget, StartDate, EndDate)
VALUES
('Website Redesign', 50000, '2023-01-10', '2023-06-15'),
('Payroll System', 75000, '2023-02-20', '2023-08-30'),
('Marketing Campaign', 120000, '2023-03-05', '2023-09-20'),
('Inventory Management', 65000, '2023-04-15', '2023-10-10'),
('Employee Training', 30000, '2023-05-01', '2023-07-31');

-- Insert sample data into EmployeeProjects

INSERT INTO EmployeeProjects (EmployeeID, ProjectID, HoursWorked)
VALUES
(1, 1, 120),
(1, 3, 80),
(2, 2, 150),
(3, 1, 90),
(3, 4, 110),
(4, 3, 70),
(5, 4, 100),

29
(5, 5, 60),
(6, 2, 130),
(7, 5, 85);

Employees Table

Departments-Table

30
Projects Table

EmployeeProjects-Table

31
SQL JOIN Types

• Joins clause is used to combine records from two or more tables in a database.
• A JOIN is a means for combining fields from two tables by using values common to each

Join Types are –

– The CROSS JOIN
– The INNER JOIN
– The LEFT OUTER JOIN
– The RIGHT OUTER JOIN
– The FULL OUTER JOIN

1. The CROSS JOIN

• A CROSS JOIN matches every row of the first table with every row of the second table
• If the input tables have x and y columns, respectively, the resulting table will have x+y
columns

The following is the syntax of CROSS JOIN −

SELECT ... FROM table1 CROSS JOIN table2 ...

1. Find the cross join between Employees and Departments tables.

Solution
--- - Create all possible combinations of employees and departments
-- (Not typically useful for business queries, but demonstrates the concept)

select *
from Employees
cross join Departments

32
Query Output:

2. The INNER JOIN (Most common join)

• An INNER JOIN creates a new result table by combining column values of two tables (table1
and table2) based upon the join-predicate.
• The query compares each row of table1 with each row of table2 to find all pairs of rows, which
satisfy the join-predicate.
• When the join-predicate is satisfied, column values for each matched pair of rows of table1
and table2 are combined into a result row.

33
• INNER JOIN statement returns only those records or rows that have matching values and is
used to retrieve data that appears in both tables.

• An INNER JOIN is the most common type of join and is the default type of join. You can
use INNER keyword optionally.
• The following is the syntax of INNER JOIN –

SELECT table1.column1, table2.column2...

FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

3. Join with Filtering (Employees in IT Department)

select [Link],[Link],[Link],[Link],[Link]
from Employees e
inner join Departments d
on [Link]=[Link]
where [Link]='IT'

4. Join with Sorting (Employees by Department and Salary)

SELECT [Link], [Link], [Link], [Link]

FROM Employees e
INNER JOIN Departments d ON [Link] = [Link]
ORDER BY [Link], [Link] DESC;

34
5. Three-Table Join (All Assignments)

SELECT
[Link] + ' ' + [Link] AS EmployeeName,
[Link],
[Link]
FROM
Employees e
INNER JOIN
EmployeeProjects ep ON [Link] = [Link]
INNER JOIN
Projects p ON [Link] = [Link]
ORDER BY
[Link], [Link];

3. The LEFT OUTER JOIN

• In LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table T1
that does not satisfy the join condition with any row in table T2, a joined row is added
with null values in columns of T2.
• Thus, the joined table always has at least one row for each row in T1.
• The following is the syntax of LEFT OUTER JOIN −

SELECT ...
FROM table1 LEFT OUTER JOIN table2
ON conditional_expression ...
6. LEFT JOIN (or LEFT OUTER JOIN)
- -Get all employees and their departments (including employees without departments)

SELECT *
FROM Employees e
LEFT JOIN Departments d ON [Link] = [Link];

4. The RIGHT OUTER JOIN

• In RIGHT OUTER JOIN, an inner join is performed first. Then, for each row in
table T2 that does not satisfy the join condition with any row in table T1, a joined
row is added with null values in columns of T1.
• Thus, the joined table always has at least one row for each row in T2.

35
 • The following is the syntax of RIGHT OUTER JOIN −

SELECT ...
FROM table1 RIGHT OUTER JOIN table2
ON conditional_expression ...

7. RIGHT JOIN (or RIGHT OUTER JOIN)

-- Get all departments and their employees (including departments without

employees)

SELECT *
FROM Employees e
RIGHT JOIN Departments d ON [Link] = [Link];

5. FULL JOIN (or FULL OUTER JOIN)

• First, an inner join is performed. Then, for each row in table T1 that does not satisfy
the join condition with any row in table T2, a joined row is added with null values
in columns of T2. In addition, for each row of T2 that does not satisfy the join
condition with any row in T1, a joined row with null values in the columns of T1 is
added.

• The following is the syntax of FULL OUTER JOIN −

SELECT ...
FROM table1 FULL OUTER JOIN table2
ON conditional_expression ..

8. FULL JOIN (or FULL OUTER JOIN)

-- Get all employees and all departments

SELECT *
FROM Employees e

FULL JOIN Departments d ON [Link] = [Link];

36
8. Non-Equi Join (join condition not using equals)
-- Find projects that started after the employee was hired

SELECT [Link] + ' ' + [Link] AS EmployeeName,

[Link],
[Link],
[Link]
FROM Employees e
JOIN EmployeeProjects ep ON [Link] = [Link]
JOIN Projects p ON [Link] = [Link]
WHERE [Link] > [Link];

Subqueries

A subquery (or inner query/nested query) is a SQL query nested inside another SQL query
(typically in the WHERE, FROM, or SELECT clause). Subqueries allow you to break down
complex problems into logical parts.

Types of Subqueries

1. Scalar subquery - Returns a single value

2. Row subquery - Returns a single row with multiple columns
3. Column subquery - Returns a single column with multiple rows
4. Table subquery - Returns a result set that can be treated as a table

37
Subquery Examples Using the Employee Database

1. WHERE Clause Subquery (Single Value)

Find employees who earn more than the average salary:

select *
from Employees
where salary > (
select avg(Salary)
from EMployees

2. IN Operator Subquery (Multiple Values)

Find employees working on projects with budgets over $70,000

SELECT [Link], [Link]

FROM Employees e
WHERE [Link] IN (
SELECT [Link]
FROM EmployeeProjects ep
JOIN Projects p ON [Link] = [Link]
WHERE [Link] > 70000
);

Views in SQL Server

A view is a virtual table based on the result set of a SQL statement. Views allow you to:

 Simplify complex queries

 Provide security by restricting access to specific columns
 Present data in a particular way without changing the underlying tables

38
Basic Syntax for Creating Views

CREATE VIEW [schema_name.]view_name

AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Examples:

1. Create a view that lists FirstName nad LastName of all employees.

create view listEmployees

as
select FirstName,LastName
from Employees

2. Create a view that gets employees with their department information

Solution:

Create view Employees_Dept

as

from Employees e
inner join Departments d on [Link]=[Link]

39
Addition examples on joins

1. Find the cross join between tables fruit_a and fruit_b.

Select *

from basket_a cross join basket_b

40
Output of the cross join

2. Find the inner join between fruit_a and fruit_b

Solution:

select a,fruit_a,b,fruit_b
from basket_a inner join basket_b

on fruit_a=fruit_b

41
Output:

3. Find the left out join between fruit_a and fruit_b

Solution:
select a,fruit_a,b,fruit_b
from basket_a left outer join basket_b

on fruit_a=fruit_b

Output:

42
4. Find the right out join between fruit_a and fruit_b

Solution:

select a,fruit_a,b,fruit_b
from basket_a right outer join basket_b

on fruit_a=fruit_b

Output:

5. Find the full out join between fruit_a and fruit_b

Solution:

select a,fruit_a,b,fruit_b
from basket_a full outer join basket_b

on fruit_a=fruit_b

43
Additional Examples

1. List all countries with more than 2 suppliers.

SELECT Country, COUNT (Id) AS Suppliers

FROM Supplier
GROUP BY Country
HAVING COUNT(Id) > 2

2. List the number of customers in each country. Only include countries with more than 10
customers

SELECT Country, COUNT(Id) AS Customers

FROM Customer
GROUP BY Country
HAVING COUNT(Id) > 10

3. List the number of customers in each country, except the USA, sorted high to low. Only
include countries with 9 or more customers.

SELECT Country, COUNT(Id) AS Customers

FROM Customer
WHERE Country <> 'USA'
GROUP BY Country
HAVING COUNT(Id) >= 9
ORDER BY COUNT(Id) DESC

Exercise: Write SQL query for the following

1. From the items_ordered table, select a list of all items purchased for customerid 10339.
Display the customerid, item, and price for this customer.
2. Select all columns from the items_ordered table for whoever purchased a Flashlight.
3. Select the customerid, order_date, and item values from the items_ordered table for any
items in the item column that start with the letter "S".

44
4. Select the distinct items in the items_ordered table. In other words, display a listing of each
of the unique items from the items_ordered table.

Answers for the Exercise

1.

SELECT customerid, item, price

FROM items_ordered

WHERE customerid=10339;

2.

SELECT * FROM items_ordered

WHERE item = 'Flashlight';

3.

SELECT customerid, order_date, item

FROM items_ordered

WHERE item LIKE 'S%';

4.

SELECT DISTINCT item

FROM items_ordered;

45
Review Exercises

1. Select the maximum price of any item ordered in the items_ordered table. Hint: Select the
maximum price only.

2. Select the average price of all of the items ordered that were purchased in the month of Dec.

3. What is the total number of rows in the items_ordered table?

4. For all of the flashlight that were ordered in the items_ordered table, what is the price of the
lowest flashlight? Hint: Your query should return the price only.

Answers for Review Exercises

Exercise #1

SELECT MAX (price)

FROM items_ordered;

Exercise #2

SELECT AVG (price)

FROM items_ordered

WHERE order_date LIKE '%Dec%';

Exercise #3

SELECT COUNT (*)

FROM items_ordered;

Exercise #4

SELECT MIN (price) FROM items_ordered WHERE item = 'Flashlight';

46
Use empInfo table for the exercises that follow:

1. Find the output of the following query.

SELECT MAX (salary), dept

FROM empInfo

GROUP BY dept;

Answer:

OR

SELECT MAX (salary) AS sal, dept

FROM empInfo

GROUP BY dept;

47
Review Exercises

1. How many people are in each unique city in the customers table?

Select the city and display the number of people in each. Hint:

COUNT is used to count rows in a column, SUM works on numeric data only.

2. From the items_ordered table, select the item, maximum price, and minimum price for each
specific item in the table. Hint: The items will need to be broken up into separate groups.

3. How many orders did each customer make? Use the items_ordered table. Select the
customerid, number of orders they made, and the sum of their orders.

Answers for the Review Exercises

Exercise #1

SELECT city, COUNT (city)

FROM customers

GROUP BY city;

Exercise #2

SELECT item, MAX (price), MIN (price)

FROM items_ordered

GROUP BY item;

Exercise #3

SELECT customerid, COUNT (customerid), SUM (price)

FROM items_ordered

GROUP BY customerid;

48
Examples:

1. Find the output of the following query

SELECT dept, AVG (salary)

FROM employee

GROUP BY dept;

2. Find the output of the following query

SELECT dept, AVG (salary)

FROM employee

GROUP BY dept

HAVING AVG (salary) > 1200;

Use items_ordered and customers table for the exercises that follow:

Review Exercises (note: yes, they are similar to the group by exercises, but these contain
the HAVING clause requirements

1. How many peoples are in each unique city in the customers table that has more than one
person in the city? Select the city and display the number of how many people are in each if
it's greater than 1.
2. From the items_ordered table, select the item, maximum price, and minimum price for each
specific item in the table. Only display the results if the maximum price for one of the items
is greater than 190.00.
3. How many orders did each customer make? Use the items_ordered table. Select the
customerid, number of orders they made, and the sum of their orders if they purchased more
than 1 item.

49
Answers for the Review Exercises

Exercise #1

SELECT city, COUNT (city)

FROM customers

GROUP BY city

HAVING COUNT (city) > 1;

Exercise #2

SELECT item, MAX (price), MIN (price)

FROM items_ordered

GROUP BY item

HAVING MAX (price) > 190.00;

Exercise #3

SELECT customerid, COUNT (customerid), SUM (price)

FROM items_ordered

GROUP BY customerid

HAVING COUNT (customerid) > 1;

50
Example:

The following statement will select the empID, dept, FirstName, age, and salary from the
empInfo table where the dept equals 'Sales' and will list the results in Ascending (default) order
based on their Salary.

SELECT empID, dept, FirstName, age, salary

FROM empInfo

WHERE dept = 'Sales'

ORDER BY salary

If you would like to order based on multiple columns, you must separate the columns with
commas. For example:

SELECT empID, dept, FirstName, age, salary

FROM EmpInfo

WHERE dept = 'Sales'

ORDER BY salary, age DESC

Use items_ordered and customers table for the exercises that follow:

Review Exercises

1. Select the lastname, firstname, and city for all customers in the customers table. Display the
results in Ascending Order based on the lastname.
2. Same thing as exercise #1, but display the results in Descending order.
3. Select the item and price for all of the items in the items_ordered table that the price is
greater than 10.00. Display the results in Ascending order based on the price.

51
Answers for the Review Exercises

Exercise #1

SELECT lastname, firstname, city

FROM customers

ORDER BY lastname;

Exercise #2

SELECT lastname, firstname, city

FROM customers

ORDER BY lastname DESC;

Exercise #3

SELECT item, price

FROM items_ordered

WHERE price > 10.00

ORDER BY price ASC;

52
Examples:

1. Find the output of the following query.

SELECT EmpID, firstname, lastname, city, salary

FROM EmpInfo

WHERE (salary >= 500.00) AND (city = 'A/A');

2.

SELECT firstname, lastname, city, salary

FROM EmpInfo

WHERE (city = 'A/A') OR (city = 'Awassa');

Review Exercises

1. Select the customerid, order_date, and item from the items_ordered table for all items unless
they are 'Shoes' or if they are ‘Sharpner’. Display the rows as long as they are not either of these
two items.

2. Select the item and price of all items that start with the letters 'S', 'P', or 'F'.

Answers for the Review Exercises

Exercise #1

SELECT customerid, order_date, item

FROM items_ordered

WHERE (item <> 'Shoes') AND (item <> 'Sharpener');

Note: Yes, that is correct, you do want to use an AND here. If you were to use an OR here, then
either side of the OR will be true, and EVERY row will be displayed. For example, when it
encounters 'Sharpener', it will evaluate to True since 'Sharpener' are not equal to 'Shoes'.

53
Exercise #2

SELECT item, price

FROM items_ordered

WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%');

Based on empinfo table, do the following exercises:

Exercise 1: Write a SQL statement that could retrieve any first names that start with ‘A’ and
show only the FirstName, LastName and city columns.

Answer:

USE mydatabase

SELECT FirstName, LastName, city

FROM empinfo

WHERE FirstName LIKE 'A%';

Note: Strings must be in single quotes.

Exercise 2: Write a SQL statement that could retrieve any last names that end in a ‘T’ and show
only the FirstName and LastName columns.

Answer:

USE mydatabase

SELECT FirstName, LastName

FROM empinfo

WHERE LastName LIKE '%T';

54
Exercise 3: Write a SQL statement that could retrieve a row (rows) with first name equals
‘Lydia’.

Answer:

USE mydatabase

SELECT * FROM empinfo WHERE FirstName = 'Lydia'

Example

1. Find the output of the following query

SELECT EmpID, lastname, salary

FROM EmpInfo

WHERE lastname IN ('Shitaye', 'Natan', 'Mamo', 'Worku');

The IN conditional operator can be rewritten by using compound conditions using the equals (=)
operator and combining it with

OR -

with exact same output results:

SELECT EmpID, lastname, salary

FROM EmpInfo

WHERE lastname = 'Shitaye' OR lastname = 'Natan' OR lastname =

'Mamo' OR lastname = 'Worku'

55
.

2. Find the output of the following query

SELECT EmpID, age, lastname, salary

FROM EmpInfo

WHERE age BETWEEN 30 AND 40;

This statement will select the EmpID, age, lastname, and salary from the EmpInfo table where
the age is between 30 and 40 (including 30 and 40).

This statement can also be rewritten without the BETWEEN operator:

SELECT EmpID, age, lastname, salary

FROM EmpInfo

WHERE age >= 30 AND age <= 40;

You can also use NOT BETWEEN to exclude the values between your ranges.

Use items_ordered and customers table for the exercises that follow:

Review Exercises

1. Select the date, item, and price from the items_ordered table for all of the rows that have a
price value ranging from 10.00 to 80.00.

2. Select the firstname, lastname and city from the customers table for all of the rows where the
city value is either: Addis Ababa, Durame, Semera, Assosa, or Mettu.

56
Answers for the Review Exercises

Exercise #1

SELECT order_date, item, price

FROM items_ordered

WHERE price BETWEEN 10.00 AND 80.00;

Exercise #2

SELECT firstname, lastname, city

FROM customers

WHERE city IN (' Addis Ababa ', ‘Durame’, ‘Semera’, ‘Assosa’,

‘Mettu’);

57
Use the following three tables to answer the following questions.

“CustomerInfo” table

Examples:

1. Find the output of the following query

SELECT customer_info.firstname, customer_info.lastname,

[Link]

FROM customer_info, purchases

WHERE customer_info.customerid = [Link]

This particular "Join" is known as an "Inner Join" or Equijoin".

This is the most common type of "Join" that you will see or use.

58
Notice that each of the columns is always preceded with the table name and a period. This isn't
always required; however, it is good practice so that you won’t confuse which columns go with
what tables.

It is required if the name column names are the same between the two tables. I recommend
preceding all of your columns with the table names when using joins.

Note: The syntax described above will work with most Database Systems. However, in the
event that this doesn't work with yours, please check your specific database documentation.

Although the above will probably work, here is the ANSI SQL-92 syntax specification for an
Inner Join using the preceding statement above that you might want to try:

SELECT customer_info.firstname, customer_info.lastname, [Link]

FROM customer_info INNER JOIN purchases

ON customer_info.customerid = [Link]

Use items_ordered and customers table for the exercises that follow:

Review Exercises

1. Write a query using a join to determine which items were ordered by each of the customers in
the customers table. Select the customerid, firstname, lastname, order_date, item, and price for
everything each customer purchased in the items_ordered table.

2. Repeat exercise #1, however display the results sorted by city in descending order.

Answers for the Review Exercises

Exercise #1

Option One:

SELECT [Link], [Link], [Link],

59
items_ordered.order_date, items_ordered.item, items_ordered.price

FROM customers, items_ordered

WHERE [Link] = items_ordered.customerid;

Option Two:

SELECT [Link], [Link], [Link],

items_ordered.order_date, items_ordered.item, items_ordered.price

FROM customers INNER JOIN items_ordered

ON [Link] = items_ordered.customerid;

Exercise #2

Option One:

SELECT [Link], [Link], [Link],

items_ordered.item

FROM customers, items_ordered

WHERE [Link] = items_ordered.customerid

ORDER BY [Link] DESC;

Option Two:

SELECT [Link], [Link], [Link],

items_ordered.item

FROM customers INNER JOIN items_ordered

ON [Link] = items_ordered.customerid

ORDER BY [Link] DESC;

60
)

Example 1(Subqueries(Nested Queries): Suppose you have the Branch and Staff Tables shown
below; List the staffs who work in the Branch at ‘163 Main St’.

Answer:

SELECT StaffNo, fName, lName, position

FROM Staff

WHERE branchNo = (SELECT branchNo

FROM Branch

WHERE street = ‘163 Main St’;

61
The inner SELECT statement (SELECT branchNo FROM Branch WHERE street = ‘163 Main
St) finds the branch number that corresponds to the branch with street name ‘163 Main St’.
Having obtained this branch number, the outer SELECT statement then retrieves the details of all
staff who work at this branch. In other words, the inner SELECT returns a result table containing
a single value ‘B003’, corresponding to the branch at ‘163 Main St’, and the outer SELECT
becomes:

SELECT StaffNo, fName, lName, position

FROM Staff

WHERE branchNo = ‘B003’

We can think of the subquery as producing a temporary table with results that can be accessed
and used by the outer statement. A subquery can be used immediately following a relational
operator (=, <, >, <=, >=, <>) in a WHERE clause, or a HAVING clause. The subquery itself is
always enclosed in parentheses.

Example2:- Suppose you have the Branch and Staff Tables shown above and another table
named PropertyForRent; List the properties that are handled by staff who work in the branch at
‘163 Main St’? PropertyForRent

62
Answer:

SELECT propertyNo, street, city, postcode, type, rooms, rent

FROM propertForRent

WHERE staffNo IN(SELECT staffNo

FROM Staff

WHERE branchNo = (SELECT branchNo

FROM Branch

WHERE street = ‘163 Main St’))

Working from the innermost query outwards, the first query selects the number of the branch at
‘163 Main St’. The second query then selects those staffs who work at this branch number. In
this case, there may be more than one such row found, and so we can’t use the equality condition
(=) in the outermost query. Instead, we use the IN keyword. The outermost query then retrieves
the details of the properties that are managed by each member of staff identified in
the middle query. The result table is shown below:

63
Using a subquery with an aggregate function

Example3:- List all staff whose salary is greater than the average salary, and show by how much
their salary is greater than the average.

Answer:

SELECT staffNO, fName, position, salary – (SELECT AVG(salary)FROM Staff) As SalDiff

FROM Staff

WHERE salary > (SELECT AVG(salary) FROM Staff)

First, note that we can’t write ‘WHERE salary > AVG(salary)’ because aggregate functions
can’t be used in the WHERE clause. Instead, we use a subquery to find the average salary, and
then use the outer SELECT statement to find those staff with a salary greater than this average.
In other words, the subquery returns the average salary as 17000. Note also the use of the scalar
subquery in the SELECT list, to determine the difference from the average salary. The outer
query is reduced then to:

SELECT staffNO, fName, position, salary – 17000 As SalDiff

FROM Staff

WHERE salary > 17000

The result table is shown below:

64
Note: When a subquery is one of the two operands involved in a

comparison, the subquery must appear on the right-hand side of the comparison. For example, it
would be incorrect to express the last example as:

SELECT staffNo, fName, lName, position, salary

FROM Staff

WHERE (SELECT AVG(salary)FROM Staff) < salary

What would be the results of the following sample select statements

(Assume you are using mydatabase database)?

SELECT FirstName, LastName, city FROM empinfo;

SELECT LastName, city, age FROM empinfo WHERE age > 30;

SELECT FirstName, LastName, city FROM empinfo WHERE FirstName

LIKE 'A%';

SELECT * FROM empinfo;

SELECT FirstName, LastName FROM empinfo WHERE FirstName LIKE

‘A%”, LastName LIKE '%S';

SELECT FirstName, LastName, age FROM empinfo WHERE LastName LIKE

'%e%';

65
Select statement exercises (Assume you are using mydatabase database)

1. Display everyone's first name and their age for everyone that's in table.

SELECT FirstName, age FROM empinfo;

2. Display the first name, last name, and city for everyone that's not from A/A.

SELECT FirstName, LastName, city FROM empinfo WHERE city <>

'A/A';

3. Display all columns for everyone that is over 40 years old.

SELECT * FROM empinfo WHERE age > 40;

4. Display the first and last names for everyone whose last name ends in an "an".

SELECT FirstName, LastName FROM empinfo WHERE LastName LIKE

'%an';

5. Display all columns for everyone whose first name equals

"Aster".

SELECT * FROM empinfo WHERE FirstName = 'Aster';

6. Display all columns for everyone whose first name contains

"Mar".

SELECT * FROM empinfo WHERE FirstName LIKE '%Mar%'

66
Example[ Views] Run the following

CREATE TABLE t (qty INT, price INT);

INSERT INTO t VALUES(3, 50);

CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

SELECT * FROM v;

A view definition is subject to the following restrictions:

Any table or view referred to in the definition must exist.

However, after a view has been created, it is possible to drop a table or view that the definition
refers to. To check a view definition for problems of this kind, use the CHECK TABLE
statement.

The tables named in the view definition must already exist.

You cannot associate a trigger with a view.

ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a
statement that has its own ORDER BY.

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE,
or INSERT to update the contents of the underlying table. For a view to be updatable, there must
be a one-to relationship between the rows in the view and the rows in the underlying table. There

67
are also certain other constructs that make a view non-updatable. To be more specific, a view is
not updatable if it contains any of the following:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

 DISTINCT
 GROUP BY
 HAVING
 Subquery in the select list
 Join
 A subquery in the WHERE clause that refers to a table in the

FROM clause With respect to insertability (being updatable with INSERT statements), an
updatable view is insertable if it also satisfies these additional requirements for the view columns:

There must be no duplicate view column names.

The view must contain all columns in the base table that do not have a default value.

The view columns must be simple column references and not derived columns. A derived column
is one that is not a simple column reference but is derived from an expression. These are examples
of derived columns: col1 + 3, col3 / col4, etc

A view that has a mix of simple column references and derived columns is not insertable, but it
can be updatable if you update only those columns that are not derived. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

This view is not insertable because col2 is derived from an expression. But it is updatable if the
update does not try to update col2. This update is allowable:

UPDATE v SET col1 = 0;

This update is not allowable because it attempts to update a derived column:

UPDATE v SET col2 = 0;

68
ALTER VIEW Syntax

ALTER VIEW view_name [(column_list)]

AS select_statement

This statement changes the definition of an existing view. The syntax is similar to that for
CREATE VIEW. This statement requires the

CREATE VIEW and DELETE privileges for the view, and some privilege for each column
referred to in the SELECT statement.

DROP VIEW Syntax

DROP VIEW [IF EXISTS]

view_name1 [, view_name2] ...

[RESTRICT | CASCADE]

DROP VIEW removes one or more views. You must have the DROP privilege for each view.

You can use the keywords IF EXISTS to prevent an error from occurring for views that don't
exist. When this clause is given, a NOTE is generated for each non-existent view. RESTRICT
and CASCADE, if given, are parsed and ignored.

69
The Schema shown below used in this document.

 Customer: Stores information about customers (e.g., customer ID, name, email,
address).
o Includes:
 Id
 FirstName
 LastName
 City
 Country
 Phone

 Product: Stores details about products (e.g., product ID, name, price).
Includes:
 Id
 ProductName
 SupplierId
 UnitPrice
 Package
 IsDicontinued

70
  Order: Represents a transaction where a customer purchases one or more products
(e.g., order ID, date, total amount).

Includes:
 Id
 OrderDate
 CustomerId
 TotalAmount

ORDERITEM: connects orders to products.

Includes:
 Id
 OrderId
 ProductId
 UnitPrice
 Qauntity

Supplier: One supplier can provide multiple products, but each product is supplied by only one
supplier
Includes:
 Id
 ComapnyName
 ContactName
 City
 Country
 Phone
 Fax

Relationships

Customer to Order:

 Relationship: One-to-many.
 One customer can place multiple orders, but each order is linked to only one customer.
 Keys: CustomerID (primary key in Customer) is a foreign key in the Order table.

Order to OrderDetails:

 Relationship: One-to-many.
 One order can contain multiple line items (products), but each line item belongs to only
one order.

71
  Keys: OrderID (primary key in Order) is a foreign key in the OrderDetails table.

Product to OrderDetails:

 Relationship: One-to-many.
 One product can appear in multiple order line items, but each line item references only
one product.
 Keys: ProductID (primary key in Product) is a foreign key in the OrderDetails table

Order ↔ Product:

 Relationship: Many-to-many (resolved via OrderDetails).

 One order can include multiple products, and one product can be part of multiple orders.
The OrderDetails junction table resolves this by linking OrderID and ProductID.

Supplier to Product:

 Relationship: One-to-many.
 One supplier can provide multiple products, but each product is supplied by only
one supplier (assuming a single-supplier model for simplicity; this could be many-to-
many with a junction table if multiple suppliers per product are allowed).

Keys: SupplierID (primary key in Supplier) is a foreign key in the Product table.

Customer table

72
Order table

Supplier table

73
Product table

OrderItem table

74