Unit II : SQL

SQL

1. Discuss the attribute data types available in SQL.

2. Exemplify the basic INSERT, DELETE, and UPDATE operations in SQL.

3. Illustrate the use of Aggregate functions in SQL.

4. What are co-related nested queries? Explain with example.

5. Elaborate the working of the ALTER command with an example.

6. Illustrate the use of basic substring matching operators in SQL.

---

Data Definition and Integrity Constraints

1. Create Relations with Constraints

Given Database:

Student(sno: integer, sname: string, major: string, level: string, age: integer)  
Course(cname: string, meetsat: time, room: string, fid: integer)  
Enrolled(sno: integer, cname: string)  
Faculty(fid: integer, fname: string, deptid: integer)

Write SQL statements to create the above relations including all primary and foreign key constraints.

---

2. Create Relations with Constraints

Given Relations:

Emp(eid: integer, ename: string, age: integer, salary: real)  
Works(eid: integer, did: integer, pcttime: integer)  
Dept(did: integer, dname: string, budget: real, managerid: integer)

Write SQL data definition statements with primary and foreign key constraints.

---

3. Business Trips Database

Relations:

Salesperson(Ssn, Name, Start_Year, Dept_no)  
Trip(Ssn, From_city, To_city, Departure_date, Return_date, Trip_id)  
Expense(Trip_id, Account#, Amount)  
Account(AccountNo, Name, Bank, Branch)

Write SQL DDL statements for this relation that keeps track of the trips of sales persons in sales office. Specify primary and foreign keys for this schema. State any assumptions made.

---

SQL Query Questions

4. Employee-Company Database

Relational Schema: Consider the relational schema where the primary keys are underlined

Employee(emp_name, street, city)  
Works(emp_name, cmp_name, salary)  
Company(cmp_name, city)

Queries:

i) Find the names of all employees who work in TCS.
ii) Find names, street, and city of employees who work for TCS and earn more than 10,000.
iii) Find the sum of salaries in the Works relation.
iv) Retrieve number of employees and average salary for those working in Infosys.

v) Company name, number of employees, and average salary at Infosys.

---

5. Company Database Queries

Schema: Consider a company database with relations:

Employee(ssn, name, salary, deptno)  
Department(deptno, dname, mgrssn)  
Project(Pno, Pname, deptno)

Queries:

i) List employee names and their corresponding department names.
ii) Display total salary of employees per department.
iii) Display salaries of ‘Research’ department employees after 10% raise.
iv) Display sum, max, min, and average salary of all employees.

---

6. Bank Database Queries

Schema: Consider the bank database:

Account(account_no, branch_name, balance)  
Branch(branch_name, branch_city, assets)  
Customer(customer_name, customer_street, customer_city)  
Borrower(customer_name, loan_number)  
Loan(loan_number, branch_name, amount)  
Depositor(customer_name, account_number)

Queries:

i) Find names of branches in “Chicago”.
ii) Find borrowers with a loan in branch “Downhil”.
iii) List branches with customers who live in Harrison and have an account.
iv) Count number of customers with an account.

---

7. Student-Project Queries

Schema: Assume there are three relations

STUDENT(Stud_No, Stud_Name)  
ASSIGNED_TO(Stud_No, Project_No)  
PROJECT(Project_No, Project_Name)

Queries:

i) Get Stud_No and Stud_Name of students working on database projects.
ii) Get students working on both projects P-50 and P-70.
iii) Get students not working on project P-10.
iv) Delete student with Stud_No = 11.

Queries:

i) Students working on project ‘DB1’.
ii) Students working on both projects P-50 and P-70.
iii) Students not working on project P-70.
iv) Delete student with Stud_No = 11.

PL/SQL

1. List and briefly explain the various types of blocks in PL/SQL.

2. What is the purpose of PL/SQL? Explain the types of blocks in PL/SQL.

3. Distinguish between procedures and functions in PL/SQL.

---

4. Write a procedure that decreases the salary by 10% for all employees earning more than Rs. 10,000.

5. Write a procedure to update the salary of an employee (by emp-id) in the employee table by 10%.

6. Write an update trigger that fires whenever someone tries to update the salary field in the employee table.

7. List the different types of triggers. Write an update trigger that fires on update of salary in the employee table.