 This file is a complete SQL revision for the mid-term and final exams in

the database #1 course, containing all exam questions and

assignments.

 Last updated in 2023.

 Created by Mohamed Ali Mohamed

 Create Table:
a) Consider the following relational structure of a library loandatabase:
Book ( book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation ( borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements (DDL) to achieve the following:

1) Create the Book and Loan tables.

Include thePrimary/Foreign key constraints.

Create Table Book (

book# int Not Null ,

Title varchar(50) ,

Author Varchar(50),

Publisher Varchar(50),

Year date,
Shelf Varchar(50),
Primary key(book#)
);

Page | 1
Create Table Loan (

borrower# int Not Null,

book# int Not Null ,

date_due_back Date,
Primary Key (borrower#, book#),
Foreign key (borrower#) References Borrower (borrower#)

ON Update Cascade
ON delete Cascade ,
Foreign key (book#) References Book (book#)

ON Update Cascade
ON delete Cascade
);
b) Consider the following relational structure of a COMPANYdatabase:
EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
Write the SQL statements (DDL) for the following:

Page | 2
2) Create the PROJECT and WORKS-ON tables.
Include thePrimary/Foreign key constraints.

Create Table Project (

PNO int Not Null ,
PName VarChar(50) Not Null,

DNO int Not Null,

Budget money,
PMGR# int Not NUll,
Primary Key (PNO),
Foreign Key (DNO) References Department (DNO)
ON Update Cascade
ON delete Cascade,
Foreign Key (PMGR#) References Empoloyee (ENO)

ON Update Cascade
ON delete Cascade,
);

Page | 3
Create Table WORKS_ON (
ENO int Not NUll,

PNO int Not Null,

HOURS decimal,
Primary Key (ENO, PNO),
Foreign Key (ENO) REFERENCES Empoloyee (ENO)

ON Update Cascad
ON delete Cascade ,

Foreign Key (PNO) REFERENCES Project (PNO)

ON Update Cascade
ON delete Cascade ,
);
Consider the relational structure of the sales / order database:
Customer(CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER(ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT(ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT(PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following :

Page | 4
3) Create the ORDERPRODUCT table, Include the Primary /Foreign
key constraints
Create Table Orderproducts (

Order# int NOT NULL,

Product# int NOT NULL,

QTY_Ordered VarChar(50) ,
PRIMARY KEY (order#, Product#),
FOREIGN KEY (order#) REFERENCES orders(order#)

ON Update Cascade

ON delete Cascade ,
FOREIGN KEY (product#) REFERENCES Products(Product#)

ON Update Cascade
ON delete Cascade
);
Consider the following relational structure of Educationaldatabase:
• Department (dno, dname, manager)
• Student (stud#, sname, city, birthday, dno)
• Course (course#, cname, dno, credit_hours)
• Enrollment (stud#, course#, year, semester, score)

Page | 5
4) Create the Student table. Include thePrimary/Foreignkey constraints

CREATE TABLE Students (

stud# VarChar(50) Not NUll ,

sname VARCHAR(50),
city VARCHAR(50),
birthday DATE,
dno VarChar(50) Not NUll,
Primary Key (Stud#) ,
FOREIGN KEY (dno) REFERENCES Departments(dno)

ON Update Cascade
ON delete Cascade
);

Consider the following relational structure of EducationalDatabase:

Department (dno, dname, headOfDepartment)

Student (stud#, sname, city, birthdate, score)

Course (Course#, cname, dno, credit-hours)

Enrollment (stud#, Course#, year, semester, score)

Page | 6
5) Create the Enrollment table. Include thePrimary/Foreign key
constraints
Create Table Enrollment (
stud# varchar(50) Not Null,
Course# varchar(50) Not Null,
year date,
semester varchar(20),
score varchar(100),
PRIMARY KEY (stud#, Course#),
FOREIGN KEY (stud#) REFERENCES Student(stud#)
ON Update cascade
ON delete cascade,
FOREIGN KEY (Course#) REFERENCES Course(Course#)
ON Update cascade
ON delete cascade,
);

Consider the relational structure of the Sales/Order database:

CUSTOMER (CUSTOMER#, NAME, ADDRESS, CREDIT-LIMIT)

ORDER (ORDER#,CUSTOMER#, DATE, TOTAL-VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY-ORDERED)

PRODUCT (PRODUCT#, DESCRIPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 7
6)Create the ORDER table. Include the Primary/Foreign key
constraints.

CREATE TABLE ORDER (

ORDER# varchar(50) Not Null,

CUSTOMER# varchar(50) Not Null,

DATE date,
TOTAL-VALUE varchar(50),
Primarykey (order#),
FOREIGN KEY (CUSTOMER#) REFERENCES CUSTOMER (CUSTOMER#)
ON Update Cascade

ON delete Cascade
);

Page | 8
 Alter Table:
a) Consider the following relational structure of a library loandatabase:
Book ( book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation ( borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements (DDL) to achieve the following:
1)Add a new column phone to the Borrower table:
Alter Table Borrower
Add phone Varchar(50) Not Null;
b) Consider the following relational structure of a COMPANYdatabase:
EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
Write the SQL statements (DDL) for the following:

2) Add a new column project location PLOCATION to the

DEPARTMENT table.

Alter Table Department

ADD Plocation VarChar(50) Not null;

Page | 9
b) Consider the following relational structure of a COMPANYdatabase:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

3) Add a new default value 4000 pound to the attributeCREDIT_LIMIT

ALTER TABLE Customer
ALTER COLUMN CREDIT_LIMIT SET DEFAULT 4000;
4) Add a new column customer phone (PHONE) to theCUSTOMER table

Alter Table Customers

Add phone Varchar(50) Not Null;
EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

5) Add a new default value Cairo to the attribute CITY.

ALTER TABLE CUSTOMER
ALTER COLUMN CITY SET DEFAULT 'Cairo';

Page | 10
b) Consider the following relational structure of a COMPANYdatabase:
Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)
6) Add a new column Reference to the Course table
ALTER TABLE Courses
ADD Reference VARCHAR(50);
Consider the relational structure of the Sales/ Order
database:Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)
7) Add a new customer address to the Customer table
ALTER TABLE Customer
ADD address VARCHAR(50);

Page | 11
 Define a new constraint:
a) Consider the following relational structure of a library loandatabase:
Book ( book# , Title, Author, Publisher, Year, Shelf)
Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation ( borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements (DDL) to achieve the following:
1) Define a new constraint valid-status in the Loan- statustable that status
legal values are staff, undergraduate, postgraduate, and visitor

Alter Table Loan_Status

ADD Constraint valid_status

Check (status IN ('staff', 'undergraduate', 'postgraduate', 'visitor'));

b) Consider the following relational structure of a COMPANYdatabase:

EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
Write the SQL statements (DDL) for the following:

Page | 12
2) Define a new constraint valid-budget that the BUDGET of aPROJECT is in the
range 15000 to 2500000

Alter Table Project

ADD Constraint valid_budget
Check (budget Between 15000 AND 2500000);

OR:
ALTER TABLE PROJECT
ADD CONSTRAINT valid-budget
CHECK (BUDGET >= 15000 AND BUDGET <= 2500000);

Consider the relational structure of the sales/order database:

Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 13
3) Define a new constraint valid_credit_limit that the CREDIT_LIMIT of a
CUSTOMER is in the range 3000 to 1200pound

ALTER TABLE customers

ADD CONSTRAINT valid_credit_limit
CHECK (credit_limit Between 3000 AND 12000);
OR:
ALTER TABLE customers
ADD CONSTRAINT valid_credit_limit
CHECK (credit_limit >= 3000 AND credit_limit <= 12000);
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

4) Define a new constraint valid_semester in the Enrollmenttable that

semester legal values are fall, spring, and summer

ALTER TABLE Enrollments

ADD CONSTRAINT valid_semester
CHECK (semester IN ('fall', 'spring', 'summer'));

Page | 14
 Define the SQL view:
a) Consider the following relational structure of a library loandatabase:
Book ( book# , Title, Author, Publisher, Year, Shelf)Borrower
(borrower#, name , city, status)
Loan (borrower#, book#, date-due-back) Reservation ( borrower# ,
book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements (DDL) to achieve the following:
1) Define the SQL view consisting of the borrower number forall borrowers
reserve more than 3 books.
Create View VW AS

SELECT borrower#

FROM Reservation

GROUP BY borrower#

HAVING COUNT(*) > 3;

b) Consider the following relational structure of a COMPANYdatabase:

EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

Write the SQL statements for the following :

2) Define the SQL view consisting of the employee number forall employees
their averge working hours less than 12 hours
Page | 15
CREATE View VW AS
Select eno

FROM Works_on

GROUP BY eno
HAVING AVG (hours) < 12 ;
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

3)Define the SQL view consisting of the order number for all orders contain more
than 8 products

CREATE VIEW VW AS
SELECT order#
FROM orderproducts
GROUP BY order#
HAVING COUNT(*) > 8;

Page | 16
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

3) Define the SQL view consisting of the course number for allcourses with total
enrollment greater than 20

CREATE VIEW VW AS
SELECT course#
FROM Enrollments GROUP BY course#
HAVING COUNT(*) > 20;

Consider the following relational structure of Educationaldatabase:

CUSTOMER (CUSTOMER#, NAME, CITY, CREDIT-LIMIT)
ORDER (ORDER#, CUSTOMER#, DATE, TOTAL-VALUE)
ORDERPRODUCT (ORDER#, PRODUCT#, QTY-ORDERED)
PRODUCT (PRODUCT#, PRODUCTNAME, PRICE, BALANCE)
4) Define the SQL view consisting of the order number for allorders with an
average quantity ordered greater than 500.

Page | 17
CREATE VIEW VW AS

SELECT ORDER#
FROM ORDERPRODUCT
GROUP BY ORDER#
HAVING AVG(QTY_ORDERED) > 500;

Consider the following relational structure of EducationalDatabase:

Department (dno, dname, headOfDepartment)
Student (stud#, sname, city, birthdate, score)

Course (Course#, cname, dno, credit-hours)

Enrollment (stud#, Course#, year, semester, score)

5) Define SQL View consisting of the student number for all studentswith total
enrolllment less than 3
CREATE VIEW VW AS
SELECT stud#
FROM Enrollment
GROUP BY stud#
HAVING COUNT(*) < 3;
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Page | 18
Write the SQL statements for the following:
6) Define SQL View consisting of the product number for all productsordered in
more than 10 orders
CREATE VIEW VW AS
SELECT PRODUCT#
FROMORDERPRODUCT
GROUP BY PRODUCT#
HAVING COUNT(*) > 10;

b) Consider the following relational structure of a COMPANYdatabase:

EMPLOYEE ( ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
Write the SQL statements for the following :
7) Define SQL View consisting of the employee number for allempolyees
working in more than 3 projects
CREATE VIEW VW AS
SELECT ENO
FROM WORKS-ON
GROUP BY ENO
HAVING COUNT(*) > 3;

Page | 19
8) Define SQL View consisting of the employee number for allempolyees
their total working hours more than 48 hours
CREATE VIEW VW AS
SELECT ENO
FROM WORKS-ON
GROUP BY ENO
HAVING SUM(HOURS) > 48;

Page | 20
 Anything that starts with get, see where we will get what is required
from any table or tables
a) Consider the relational structure of Library database:

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements(DML) to the following:

Get Full details:

1. Get full details of all borrowers of status visitor in namealphabetical
order.

SELECT *

FROM Borrower

WHERE status = 'visitor'

ORDER BY name ASC;

Page | 21
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)

PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)

WORKS-ON (ENO, PNO, HOURS)

2. Get full details of all employees working in departmentnumber D4 in

alphabetical name order.
SELECT *

FROM EMPLOYEE

WHERE DNO = 'D4'

ORDER BY ENAME ASC;

Consider the following relational structure of Educationaldatabase:

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

Page | 22
3) Get full details of all students of department number D3 instudent name
alphabetical order
SELECT *

FROM Students

WHERE dno = 'D3'

ORDER BY Sname ASC;

Consider the following relational structure of EducationalDatabase:

Department (dno, dname, headOfDepartment)

Student (stud#, sname, city, birthdate, score)

Course (Course#, cname, dno, credit-hours)

Enrollment (stud#, Course#, year, semester, score)

4)Get full details of all courses belong department number D2in credit hours
descending order

SELECT *
FROM Course
WHERE dno = 'D2'
ORDER BY `credit-hours` DESC;

Page | 23
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)
Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

5)Get full details of all courses belong department number D2in course name
alphpitical order
SELECT *
FROM Course
WHERE dno = 'D2'
ORDER BY cname ASC;

 Get the total number: [ Total Number== Count(*)] ###

a) Consider the relational structure of Library database:

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements(DML) to the following:

Page | 24
1. Get the total number of books shelved on shelf Q14.
SELECT COUNT(*) As TotalBooks
FROM Book
WHERE [Link] = 'Q14';
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
2. Get the total number of employees assigned in the
department number D5
SELECT COUNT(*) AS TotalEmployees

FROM EMPLOYEE
WHERE DNO = 'D5';
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Page | 25
Write the SQL statements for the following:

3) Get the total number of customers with credit limit lessthan

6000

SELECT COUNT(*) AS total_customers

FROM Customers
WHERE credit_limit < 6000;

Consider the following relational structure of Educationaldatabase:

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

4) Get the total number of courses of department number D1

SELECT COUNT(*) AS total_courses

FROM Courses
WHERE dno = 'D1';

Page | 26
Consider the following relational structure of EducationalDatabase:
Department (dno, dname, headOfDepartment)

Student (stud#, sname, city, birthdate, score)

Course (Course#, cname, dno, credit-hours)

Enrollment (stud#, Course#, year, semester, score)

5)Get the total number of students in department nummberD3
SELECT COUNT(*) AS TotalStudents
FROM Student JOIN Department
ON [Link] = [Link]
WHERE [Link] = 'D3';
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:
6)Get the total number of orders in date 3/11/2018
SELECT COUNT(*) AS TotalOrders
FROM Order
WHERE DATE = '2018-11-03';

Page | 27
b) Consider the Company database: Write the SQL statements(DML) for the
following:
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
7) Get the total number of empolyees Assigned in departmentnumber D2
SELECT COUNT(*) AS TotalEmployees
FROM EMPLOYEE
WHERE DNO = 'D2';

 Get for each

b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)

PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)

WORKS-ON (ENO, PNO, HOURS)

Page | 28
1. Get for each project the project number and the number ofemployees working
in the project

SELECT PNO, COUNT(ENO) AS "Number of Employees"

FROM WORKS_ON
GROUP BY PNO;
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:
2) Get for each product the product number and its averagequantity orderd

SELECT PRODUCT#, AVG(QTY_ORDERED)

FROM ORDERPRODUCT
GROUP BY PRODUCT#;

Page | 29
3) Get for each product the product number and its totalquantity ordered
in all orders
SELECT PRODUCT#, SUM(QTY_ORDERED)
FROM ORDERPRODUCT
GROUP BY PRODUCT#;
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

4) Get for each project the project number and its totalworking hours
SELECT PNO, SUM(HOURS)
FROM WORKS-ON
GROUP BY PNO;

Page | 30
  Get with Join Tables:OR From one Table
a) Consider the relational structure of Library database:

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements(DML) to the following:
1. Get the title of all books reserved on the date 1/12/2021.

SELECT [Link]
FROM Book JOIN Reservation
ON [Link]# = [Link]#
WHERE Reservation.reservation_date = '1/12/2021';
2. Get the borrower name and title of all lent books due toback on
7/12/2021.
SELECT [Link] , [Link]
FROM Book JOIN Loan
ON [Link]# = [Link]#
JOIN Borrower
ON [Link]# = [Link]#
WHERE Loan.date_due_back = '7/12/2021';

Page | 31
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

3. Get the names of all employees working in at least oneproject

controlled by the department named Production.
SELECT [Link]
FROM Employee JOIN Works_On

ON [Link] = Works_On.ENO

JOIN Project
ON Works_On.PNO = [Link]
JOIN Department
ON [Link] = [Link]
WHERE [Link] = 'production';

Page | 32
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

4) Get the name and address of all customers with credit limitmore than 6000
pound in name alphabetical order.

SELECT name, address

FROM customers
WHERE credit_limit > 6000
ORDER BY name ASC;
5) Get the number and date of all orders for customer namedAhmed Aly
SELECT [Link]#, [Link]
FROM orders JOIN customers
ON [Link]# = [Link]#
WHERE [Link] = 'Ahmed Aly';

Page | 33
6) Get the description of all products ordered by customernamed Alla
Fahmy
SELECT [Link]
FROM PRODUCT JOIN ORDERPRODUCT
ON [Link]# = [Link]#
JOIN ORDER
ON [Link]# = [Link]#
JOIN CUSTOMER
ON [Link]# = [Link]#
WHERE [Link] = 'Alla Fahmy';
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

7) Get the name of all courses enrolled by student number S5

SELECT [Link]
FROM Student JOIN Enrollment
ON [Link]# = [Link]#
JOIN Course
ON [Link]# = [Link]#
WHERE [Link]# = 'S5';
Page | 34
8) Get the student name and course such that the student isenrolled
which belongs to department named Information System

SELECT [Link], [Link]

FROM Students JOIN Enrollments
ON [Link]# = [Link]#
JOIN Courses
ON [Link]# = [Link]#
JOIN Departments
ON [Link] = [Link]
WHERE [Link] = 'Information System';

Consider the following relational structure of Educationaldatabase:

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

Page | 35
9) Get the name of all students enrolled in course numberCS232
SELECT [Link]
FROM Student JOIN Enrollment
ON [Link]# = [Link]#
JOIN Course
ON [Link]# = [Link]#
WHERE [Link]# = 'CS232';
10) get the student name and course name such that the student isenrolled in
the course which belonges to department named computer science

SELECT [Link], [Link]

FROM course JOIN Enrollment
ON [Link]# = [Link]#
JOIN student
ON [Link]#= [Link]#
JOIN Department
ON [Link] = [Link]
WHERE [Link] = 'Computer Science';

Page | 36
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:
11) Get the description of all products order by customernamed Aly zaki
SELECT [Link]
FROM PRODUCT JOIN ORDERPRODUCT
ON [Link]# = [Link]#
JOIN ORDER
ON [Link]# = [Link]#
JOIN CUSTOMER
ON [Link]# = [Link]#
WHERE [Link] = 'Aly Zaki';

Page | 37
12) Get the number and date of all orders with total valuegreater than
10000in date desending order

SELECT ORDER#, DATE

FROM ORDER
WHERE TOTAL_VALUE > 10000
ORDER BY DATE DESC;
Consider the relational structure of the sales/order database:

Customer (CUSTOMER#, cname, phone, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_amount)
ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, pname, PRICE, BALANCE , reorder_limit)
Write the SQL statements for the following:
13) get the name and price of all products ordered bycustomer
named Aly Zaky
SELECT [Link], [Link]
FROM PRODUCT JOIN ORDERPRODUCT
ON [Link]# = [Link]#
JOIN ORDER
ON [Link]# = [Link]#
JOIN CUSTOMER
ON [Link]# = [Link]#
WHERE [Link] = 'Aly Zaky';

Page | 38
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)

PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)WORKS-

ON (ENO, PNO, HOURS)

14) Get the name and location of all projects controlled with department
with number D3 In desending order of the budget

SELECT [Link], [Link]

FROM PROJECT JOIN DEPARTMENT
ON [Link] = [Link]

WHERE [Link] = 'D3'

ORDER BY [Link] DESC;

Page | 39
15) get the names of projects controlled by the departmentnamed
elecrronics
SELECT [Link]
FROM PROJECT JOIN DEPARTMENT
ON [Link] = [Link]
WHERE [Link] = 'Electronics';
16) get the names of all employees working in at least oneproject
controlled by the department named engineering
SELECT DISTINCT [Link]
FROM EMPLOYEE JOIN WORKS-ON
ON [Link] = [Link]
JOIN PROJECT
ON [Link] = [Link]
JOIN DEPARTMENT
ON [Link] = [Link]
WHERE [Link] = 'Engineering';

Page | 40
  Get With Nested Quary:
a) Consider the relational structure of Library database:

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)

Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)
Write the SQL statements(DML) to the following:

Get the name of all borrowers has the same status as borrower number 54321
SELECT name
FROM Borrower
WHERE status = (
SELECT status
FROM Borrower
WHERE borrower# = '54321'
);

Page | 41
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
1. Get the names of all employees have the same birthday of the employee
number E1.

SELECT ename FROM Employee

WHERE BIRTHDAY = (
SELECT BIRTHDAY
FROM Employee
WHERE eno = 'E1'
);

Consider the relational structure of the sales/order database:

Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 42
3) Get the order number for all orders requested in the same date as the order
number 234567
SELECT ORDER# FROM
ORDER WHERE DATE = (
SELECT DATE
FROM ORDER
WHERE ORDER# = 234567
);

Consider the following relational structure of Educationaldatabase:

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

4) Get the name of all students has the same Department asstudent number
S7979
SELECT name
FROM Students
WHERE dno = (
SELECT dno
FROM Students
WHERE sno = 'S7979'
);
Page | 43
Consider the following relational structure of EducationalDatabase:
Department (dno, dname, headOfDepartment)
Student (stud#, sname, city, birthdate, score) Course
(Course#, cname, dno, credit-hours) Enrollment (stud#,
Course#, year, semester, score)
5) get the names of all courses has the same department Asthe course
number IS321

SELECT cname
FROM Course
WHERE dno = (
SELECT dno
FROM Course
WHERE Course# = 'IS321'
);
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 44
6) get the names of all customers have the same credit limit ofthe customer
number C4
SELECT NAME
FROM Customer
WHERE CREDIT_LIMIT = (
SELECT CREDIT_LIMIT
FROM Customer
WHERE CUSTOMER# = 'C4'
);
b) Consider the Company database: Write the SQL statements(DML) for the
following:

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)

PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)WORKS-

ON (ENO, PNO, HOURS)

7) get the names of all employees have the same birthday ofthe employee
number E3
SELECT ENAME

FROM EMPLOYEE

WHERE BIRTHDAY = (
SELECT BIRTHDAY
FROM EMPLOYEE
WHERE ENO = 'E3'
);
Page | 45
Consider the following relational structure of EducationalDatabase:
Department (dno, dname, headOfDepartment) Student
(stud#, sname, city, birthdate, score) Course (Course#, cname,
dno, credit-hours) Enrollment (stud#, Course#, year, semester,
score)
8) Get the names of all students has the same birthday As thestudent number
S123
SELECT sname
FROM Student
WHERE birthdate = (
SELECT birthdate FROM Student
WHERE stud# = 'S123'
);

b) Consider the Company database: Write the SQL statements(DML) for the
following:
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
9) Get the names of all Employeeshas the same departmentAs the Empolyee
number E6

Page | 46
SELECT ENAME
FROM EMPLOYEE
WHERE DNO = (
SELECT DNO
FROM EMPLOYEE
WHERE ENO = 'E6'
);

 Delete With Nested Quary:

Consider the following relational structure of Educationaldatabase:

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

1. Delete all enrollments of the course named SoftwareEngineering

DELETE
FROM Enrollment
WHERE course# = (
SELECT course#
FROM Course
WHERE cname = 'Software Engineering'
);

Page | 47
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)
Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

2. Delete All enrolments for the student named Shrerif Kamal

DELETE
FROM Enrollment
WHERE stud# = (
SELECT stud#
FROM Student
WHERE sname = 'Shrerif Kamal'
);
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 48
3. Delete All order products for the product named product

DELETE
FROM OrderProduct
WHERE PRODUCT# = (
SELECT PRODUCT#
FROM Product
WHERE DESCREPTION = 'product2'
);
b) Consider the Company database: Write the SQL statements(DML) for the
following:
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
4. Delete All products controlled by the department namedMechannics
DELETE
FROM Product
WHERE DNO = (
SELECT DNO
FROM Department
WHERE DNAME = 'Mechanics'
);
Page | 49
b) Consider the Company database: Write the SQL statements(DML) for the following:
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

5. Delete All products controlled by thePlanning

DELETE
FROM Product
WHERE DNO = (
SELECT DNO
FROM Department
WHERE DNAME = 'Planning'
);
Consider the following relational structure of Educationaldatabase:
Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)

Course (course#, cname, dno, credit_hours)

Enrollment (stud#, course#, year, semester, score)

Page | 50
6. Delete All enrollments of the course named computerGraphics
DELETE
FROM Enrollment
WHERE course# = (
SELECT course#
FROM Course
WHERE cname = 'Computer Graphics'
);
Consider the relational structure of the sales/order database:
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)

ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)

ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)

PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

Write the SQL statements for the following:

Page | 51
7. Delete all orders ordered in date 20/11/2021 by customernamed Khaled Aly
DELETE

FROM ORDER
WHERE DATE = '2021-11-20'
AND CUSTOMER# IN (

SELECT CUSTOMER#

FROM CUSTOMER
WHERE NAME = 'Khaled Aly'
);

2- Data Control Language (DCL):

 Can retrieve or delete:
Book (book# , Title, Author, Publisher, Year, Shelf)
Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)

1) Account A can retrieve or delete from table Reservation but not propagate
these privileges to additional accounts.

GRANT SELECT, DELETE

ON Reservation
TO A;

Page | 52
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
2)Account A can retrieve or delete from table PROJECT but not propagate these
privileges to additional accounts
GRANT SELECT, DELETE
ON PROJECT
TO A;
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
3)Account A can retrieve or delete from table Department but not propagate these
privileges to additional accounts
GRANT SELECT, DELETE
ON DEPARTMENT
TO A;
Department (dno, dname, manager)
Student (stud#, sname, city, birthday, dno)
Course (course#, cname, dno, credit_hours)
Enrollment (stud#, course#, year, semester, score)

Page | 53
4)Account A can retrieve or delete from table Enrollment but not propagate these
privileges to additional accounts

GRANT SELECT, DELETE

ON Enrollment
TO A;
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

5)Account A can retrieve or delete from table Works_ON but not propagate these
privileges to additional accounts
GRANT SELECT, DELETE
ON WORKS_ON
TO A;
 can retrieve only attributes and can grant any of these privileges

Department (dno, dname, manager)

Student (stud#, sname, city, birthday, dno)
Course (course#, cname, dno, credit_hours)
Enrollment (stud#, course#, year, semester, score)

Page | 54
1) Account B can retrieve only attributes stud#, sname, and dno of table
Student and can grant any of these privileges to other accounts

CREATE VIEW v1 AS
SELECT stud#, sname, dno
FROM Student;

GRANT SELECT
ON v1
TO B
WITH GRANT OPTION;

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)

2) Account B can retrieve only attributes name, and status of table

Borrower and can grant any of these privileges to other accounts.

CREATE VIEW v2 AS
SELECT name, status
FROM Borrower;

GRANT SELECT
ON v2
TO B
WITH GRANT OPTION;

Page | 55
 EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

3) Account B can retrieve only attributes PNAME, and LOCATION of table

PROJECT and can grant any of these privileges to other accounts

CREATE VIEW v3 AS
SELECT PNAME, LOCATION
FROM PROJECT;

GRANT SELECT
ON v3
TO B
WITH GRANT OPTION;
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)
ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)
ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

4) Account A can retrieve only attributes PRODUCT#, DESCREPTION, PRICE of

table PRODUCT and can grant any of these privileges to other accounts

CREATE VIEW v4 AS
SELECT PRODUCT#, DESCRIPTION, PRICE
FROM PRODUCT;

GRANT SELECT
ON v4
TO A
WITH GRANT OPTION;
Page | 56
CUSTOMER (CUSTOMER#, NAME, CITY, CREDIT-LIMIT)
ORDER (ORDER#, CUSTOMER#, DATE, TOTAL-VALUE) ORDERPRODUCT (ORDER#,
PRODUCT#, QTY-ORDERED)
PRODUCT (PRODUCT#, PRODUCTNAME, PRICE, BALANCE)

5) Account B can retrieve only attributes PRODUCTNAME, and PRICE of table

PRODUCT and can grant any of these privileges to other accounts.

CREATE VIEW v5 AS
SELECT PRODUCTNAME, PRICE
FROM PRODUCT;

GRANT SELECT
ON v5
TO B
WITH GRANT OPTION;
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

6)Account B can receive only Attrbuites DNo and DName of a table

department and can grant any of these privileges to other accounts
CREATE VIEW v6 AS
SELECT DNO, DNAME
FROM DEPARTMENT;

GRANT SELECT
ON v6
TO B
WITH GRANT OPTION;

Page | 57
 can update , Then Revoke:
Syntax:
GRANT UPDATE (salary)
ON EMPLOYEE
TO C;

REVOKE UPDATE (salary)

ON EMPLOYEE
FROM C;

EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

1)Account C can update attribute salary of table EMPLOYEE. Then revoke this
privilege

GRANT UPDATE (salary)

ON EMPLOYEE
TO C;

REVOKE UPDATE (salary)

ON EMPLOYEE
FROM C;

Book (book# , Title, Author, Publisher, Year, Shelf)

Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)

Page | 58
 2)Account C can update attribute price of table Book. Then revoke this
privilege

GRANT UPDATE (price)

ON Book
TO C;

REVOKE UPDATE (price)

ON Book
FROM C;
Department (dno, dname, manager)
Student (stud#, sname, city, birthday, dno)
Course (course#, cname, dno, credit_hours)
Enrollment (stud#, course#, year, semester, score)

3)Account B can update attribute credit_hour of table Course. Then revoke this
privilege

GRANT UPDATE (credit_hours)

ON Course
TO B;

REVOKE UPDATE (credit_hours)

ON Course
FROM B;

Page | 59
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)
ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)
ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

4)Account C can update attribute QTY-ORDERED of table ORDERPRODUCT.

Then revoke this privilege.

GRANT UPDATE (QTY_ORDERED)

ON ORDERPRODUCT
TO C;

REVOKE UPDATE (QTY_ORDERED)

ON ORDERPRODUCT
FROM C;
Book (book# , Title, Author, Publisher, Year, Shelf)
Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)

5) Account C can update attribute price of table Book. Then revoke this
privilege

GRANT UPDATE (price)

ON Book
TO C;

REVOKE UPDATE (price)

ON Book
FROM C;

Page | 60
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

6)Account C can update attribute Budget of table Project. Then revoke this
privilege
GRANT UPDATE (Budget)
ON Project
TO C;

REVOKE UPDATE (Budget)

ON Project
FROM C;
Department (dno, dname, manager)
Student (stud#, sname, city, birthday, dno)
Course (course#, cname, dno, credit_hours)
Enrollment (stud#, course#, year, semester, score)

7)Account B can update attribute Credit_hour of table Course . Then revoke this
privilege

GRANT UPDATE (Credit_hour)

ON Course
TO B;

REVOKE UPDATE (Credit_hour)

ON Course
FROM B;

Page | 61
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION) PROJECT (PNO,
PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

8)Account C can update attribute salary of table Employee. Then revoke this
privilege

GRANT UPDATE (salary)

ON EMPLOYEE
TO C;

REVOKE UPDATE (salary)

ON EMPLOYEE
FROM C;

 Define SQL integrity constraint:

• Department (dno, dname, manager)

• Student (stud#, sname, city, birthday, dno)
• Course (course#, cname, dno, credit_hours)
• Enrollment (stud#, course#, year, semester, score)

Page | 62
 1)Define SQL integrity constraint for the following: No student can be
enrolled in more than 6 courses.

CREATE ASSERTION MaxSixCourses AS CHECK (

NOT EXISTS (
SELECT stud#
FROM Enrollment
GROUP BY stud#
HAVING COUNT(course#) > 6
)
);
EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)
DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)
2)Define SQL integrity constraint for the following: no empolyee can work in
more than 3 projects at time
CREATE ASSERTION MaxThreeProjects AS CHECK (
NOT EXISTS (
SELECT ENO
FROM WORKS-ON
GROUP BY ENO
HAVING COUNT(PNO) > 3
) );

Page | 63
• Department (dno, dname, manager)
• Student (stud#, sname, city, birthday, dno)
• Course (course#, cname, dno, credit_hours)
• Enrollment (stud#, course#, year, semester, score)

3) Define SQL integrity constraint for the following: no student can be enrolled in
more then 6 courses

CREATE ASSERTION MaxSixCourses AS CHECK (

NOT EXISTS (
SELECT stud#
FROM Enrollment
GROUP BY stud#
HAVING COUNT(course#) > 6
)
);

. EMPLOYEE (ENO, ENAME, PHONE, SEX, BIRTHDAY, SALARY, DNO, CITY)

DEPARTMENT (DNO, DNAME, DMGR#, START-DATE, LOCATION)
PROJECT (PNO, PNAME, DNO, BUDGET, PMGR#)
WORKS-ON (ENO, PNO, HOURS)

Page | 64
 4)Define SQL integrity constraint for the following:
no employee can work in more than 3 projects at a time
CREATE ASSERTION MaxThreeProjects AS CHECK (
NOT EXISTS (
SELECT ENO
FROM WORKS-ON
GROUP BY ENO
HAVING COUNT(PNO) > 3
)
);
Customer (CUSTOMER#, NAME, ADDRESS, CREDIT_LIMIT)
ORDER (ORDER#, CUSTOMER#, DATE, TOTAL_VALUE)
ORDERPRODUCT (ORDER#, PRODUCT#, QTY_ORDERED)
PRODUCT (PRODUCT#, DESCREPTION, PRICE, WEIGHT, BALANCE)

5) Define SQL integrity constraint for the following:

no customer order total value is more than his credit limit.

CREATE ASSERTION OrderWithinCreditLimit As CHECK (

NOT EXISTS (
SELECT CUSTOMER#
FROM Customer JOIN ORDER
ON [Link]# = [Link]#
WHERE ORDER.TOTAL_VALUE > Customer.CREDIT_LIMIT
));

Page | 65
Book (book# , Title, Author, Publisher, Year, Shelf)
Borrower (borrower#, name , city, status)
Loan (borrower#, book#, date-due-back)
Reservation (borrower# , book#, reservation-date)
Loan-Status (status, max-no-of books, max-loan-period)

6)Define SQL integrity constraint for the following: books published on year
2021 is lent only to borrowers with status staff

CREATE ASSERTION StaffOnly2021Books AS CHECK (

NOT EXISTS (
SELECT *
FROM Book JOIN Loan
ON [Link]# = [Link]#
JOIN Borrower
ON [Link]# = [Link]#
WHERE [Link] = '2021' AND [Link] <> 'staff'
));

Page | 66