SQL

• How to create a database user

• The commonly used data types used in an Oracle database
• Some of the tables in the imaginary store
Creating a Database User
CREATE USER user_name IDENTIFIED BY password;
Example: create user SQL_DEMO identified by sqldemo;
GRANT connect, resource TO SQL_DEMO;
Data Definition Language (DDL)
Create Table Syntax
CREATE [GLOBAL TEMPORARY] TABLE table_name (
column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
[, column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
...]
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE tab_space;
Constraints
• CHAR(length)
• VARCHAR2(length) VARCHAR2(20
• DATE
• INTEGER
• NUMBER( precision, scale ) The maximum precision supported by
the Oracle database is 38.
• BINARY_FLOAT 32-bit floating point number
• BINARY_DOUBLE: 64-bit floating point number
The customers Table
CREATE TABLE customers
( customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dob DATE, phone VARCHAR2(12) );
CREATE TABLE product_types
( product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,
name VARCHAR2(10) NOT NULL );
Foreign Key
(Master-Detail / parent-child relationship)
create table product_type(
product_type_id integer,
product_name varchar2(20),
constraint product_type_pk(product_type_id));

create table products

(product_id integer ,
product_type_id integer ,
name varchar2(30) not null,
description varchar2(50),
price number(5, 2),
constraint products_id _pk primary key(product_id),
constraint products_types_ fk foreign key(product_type_id) references product_type(product_type_id));
Create Table Syntax
CREATE [GLOBAL TEMPORARY] TABLE table_name (
column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
[, column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
...]
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE tab_space;
GLOBAL TEMPORARY TABLE
CREATE GLOBAL TEMPORARY TABLE order_status_temp (
id INTEGER,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO order_status_temp ( id, status) VALUES (1, 'New’);

DISCONNECT
CONNECT store/store_password

SELECT * FROM order_status_temp;

no rows selected
SELECT table_name, tablespace_name, temporary
FROM user_tables
WHERE table_name IN ('ORDER_STATUS2', 'ORDER_STATUS_TEMP');
Getting Information on Columns in Tables
COLUMN column_name FORMAT a15
COLUMN data_type FORMAT a10
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'PRODUCTS';
 Alter Table
ALTER TABLE to perform tasks such as
• Adding, modifying, or dropping a column
• Adding or dropping a constraint
• Enabling or disabling a constraint

• ALTER TABLE order_status2 ADD modified_by INTEGER;

• ALTER TABLE order_status2 ADD initially_created DATE DEFAULT SYSDATE NOT NULL

Dropping a Column
ALTER TABLE order_status2 DROP COLUMN initially_created;
Adding the constraint
ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_ck CHECK
(status IN ('PLACED', 'PENDING', 'SHIPPED'));
When adding a constraint, the existing rows in the table must
satisfy the constraint

Modifying the constraint

• ALTER TABLE order_status2 MODIFY status CONSTRAINT order_status2_status_nn
NOT NULL;

• ALTER TABLE order_status2 MODIFY modified_by CONSTRAINT

order_status2_modified_by_nn NOT NULL;
Adding Foreign Key Constraint

1) ALTER TABLE order_status2

ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id);

2) ALTER TABLE order_status2

ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id) ON DELETE CASCADE;
 Alter Table - Adding Virtual Column
• ALTER TABLE salary_grades ADD (average_salary AS ((low_salary + high_salary)/2));
Alter - DROP
• ALTER TABLE order_status2 DROP COLUMN modified_by;

• ALTER TABLE order_status2 DROP CONSTRAINT order_status2_status_uq;

Getting Information on the Constraints for a Column

Some Columns in the user_constraints View

Getting Information on the Constraints for a
Column
• Example of user_constraints
Truncating a Table
TRUNCATE TABLE order_status2;

Dropping a Table
DROP TABLE order_status2;

Renaming a Table
RENAME order_status2 TO order_state;
Data Manipulation Language
(DML)
Insertion

INSERT INTO customers

( customer_id, first_name, last_name, dob, phone )
VALUES ( 6, 'Fred', 'Brown', '01-JAN-1970', '800-555-1215’ );
 Insertion

INSERT INTO customers VALUES ( 6, 'Fred', 'Brown', '01-JAN-1970', '800-555-1215’ );

INSERT INTO customers( customer_id, first_name, last_name) VALUES ( 6, 'Fred', 'Brown’,);

INSERT INTO customers(last_name , customer_id, first_name) VALUES ( ‘Brown’ , 6, 'Fred');

Update
UPDATE customers SET last_name = 'Orange' WHERE customer_id = 2;

SELECT * FROM customers WHERE customer_id = 2;

Removing a Row from a Table
DELETE FROM customers WHERE customer_id = 2;
Data Query Language (DQL)
• SELECT customer_id, first_name, last_name, dob, phone FROM
customers;
Specifying Rows to Retrieve Using the
WHERE Clause
• SELECT * FROM customers WHERE customer_id = 2;
Row Identifiers
SELECT ROWID, customer_id FROM customers;

A rowid is an 18-digit number that is represented as a base-64 number. It is physical location of the
row
Row Numbers
• SELECT ROWNUM, customer_id, first_name, last_name FROM
customers;
 Performing Arithmetic
SELECT 2*6 FROM dual;

DUAL is a special, built-in system table with one column

named DUMMY and one row containing the value 'X'. Its
primary purpose is to provide a valid table in the FROM clause of
a SELECT statement when the query is not retrieving data from a
real table but instead calculating an expression or calling a system
function
Performing Arithmetic
SELECT TO_DATE('25-JUL-2007') + 2 FROM dual;
SELECT TO_DATE('02-AUG-2007') - TO_DATE('25-JUL-2007') FROM
dual;
Using Columns in Arithmetic
SELECT name, price + 2 FROM products;
Using Column Aliases
SELECT price * 2 DOUBLE_PRICE FROM products;
Combining Column Output Using Concatenation
SELECT first_name || ' ' || last_name AS "Customer Name" FROM
customers;
SELECT customer_id, first_name, last_name, dob FROM customers
WHERE dob IS NULL;

SELECT customer_id, first_name, last_name, phone FROM customers WHERE phone IS NULL
SELECT customer_id, first_name, last_name, NVL(phone, 'Unknown
phone number') AS PHONE_NUMBER FROM customers;
Displaying Distinct Rows
SELECT customer_id FROM purchases;

SELECT DISTINCT customer_id FROM purchases;

Relational Operators
SELECT product_id, name FROM products WHERE product_id > 8;

SELECT ROWNUM, product_id, name FROM products WHERE

ROWNUM <= 3;
SELECT * FROM customers WHERE customer_id <> 2;
SELECT * FROM customers WHERE customer_id > ANY (2, 3, 4);
SELECT * FROM customers WHERE customer_id > ALL (2, 3, 4);
SQL Operators
LIKE Operator
• Underscore (_): Matches one character in a specified position
• Percent (%): Matches any number of characters beginning at the specified
position
• Example: SELECT * FROM customers WHERE first_name LIKE '_o%’;

• SELECT * FROM customers WHERE first_name NOT LIKE '_o%';

LIKE Operator
SELECT name FROM promotions WHERE name LIKE '%\%%'
ESCAPE ‘\’;

NOTE: The character after the ESCAPE tells the database how to differentiate between
characters to search for and wildcards
IN Operator
• SELECT * FROM customers WHERE customer_id IN (2, 3, 5);

• SELECT * FROM customers WHERE customer_id NOT IN (2, 3, 5);

BETWEEN Operator
SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 3;
Logical Operators
SELECT * FROM customers WHERE dob > '01-JAN-1970' OR customer_id > 3;

SELECT * FROM customers WHERE dob > '01-JAN-1970' OR customer_id > 3;

Sorting Rows Using the ORDER BY Clause
• SELECT * FROM customers ORDER BY last_name;
Sorting Rows Using the ORDER BY Clause
• SELECT * FROM customers ORDER BY first_name ASC, last_name
DESC;
Sorting Rows Using the ORDER BY Clause
SELECT customer_id, first_name, last_name FROM customers
ORDER BY 1;
Data from Multiple Tables
• SELECT name, product_type_id FROM products WHERE product_id = 2;

• SELECT name FROM product_types WHERE product_type_id = 2;

• SELECT [Link], product_types.name FROM products, product_types

WHERE products.product_type_id = product_types.product_type_id AND
products.product_id = 3;
• SELECT [Link], product_types.name FROM products, product_types
WHERE products.product_type_id = product_types.product_type_id ORDER BY
[Link];
Practice Questions
Student Course Management System
Create the following three tables:
[Link]
•Student_ID (INTEGER, Primary Key)
•Name (VARCHAR)
•Gender (CHAR(1))
•City (VARCHAR)
[Link]
•Course_ID (VARCHAR, Primary Key)
•Course_Name (VARCHAR)
•Credits (INTEGER)
[Link]
•Student_ID (Foreign Key referencing Student)
•Course_ID (Foreign Key referencing Course)
•Grade (VARCHAR)
 Student_ID Name Gender City
1 Alice F Mumbai
Student Table
2 Raj M Delhi
3 Neha F Nagpur
4 Aman M Pune

Course_ID Course_Name Credits

Course Table CSE101 DBMS 4

CSE102 Operating System 3
CSE103 Networks 3

Student_ID Course_ID Grade

1 CSE101 A
Enrollment Table 1 CSE102 B
2 CSE101 B
3 CSE103 A
4 CSE101 C
Write SQL Queries
[Link] the city of student Raj to 'Hyderabad'.
[Link] the names of students who live in cities starting with "M".
[Link] all students who are enrolled in courses with Course_ID IN ('CSE101', 'CSE103').
[Link] the student names and their grades for course CSE101, ordered by grade in ascending
order.
[Link] all courses with more than or equal to 4 credits and display their names and credits.
Aggregate Functions
• SELECT AVG(price) FROM products;

• SELECT AVG(price + 2) FROM products;

• SELECT AVG(DISTINCT price) FROM products;

 GROUP BY Clause to Group Rows

• SELECT product_type_id FROM products GROUP BY product_type_id;

• SELECT product_id, customer_id FROM purchases GROUP BY product_id, customer_id;

• SELECT product_type_id, COUNT(ROWID) FROM products GROUP BY product_type_id ORDER

BY product_type_id;
• SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id;

• Limit the number of rows where average > 20

SELECT product_type_id, AVG(price)

FROM products
GROUP BY product_type_id
HAVING AVG(price) > 20;
Display average price of only those products where price < 15. Also
display only those rows where average price is >13

SELECT product_type_id, AVG(price)

FROM products
WHERE price < 15
GROUP BY product_type_id
HAVING AVG(price) > 13
ORDER BY product_type_id;
• select table_name from user_tables;

• TABLE_NAME
• ------------------------------
• DEPT
• EMP
• BONUS
• SALGRADE
• EMPLOYEE
• EMP_COMPANY
• CUSTOMERS
• STUDENT
• COURSE
• ENROLLMENT
 Loan Table Account Table
loan_number branch_name amount
L-1 Downtown 1000 account_number branch_name balance
L-2 Perryridge 2000 A-101 Perryridge 1000
L-3 Mianus 500 A-102 Downtown 200
L-4 Perryridge 1200 A-103 Perryridge 3000
L-5 Perryridge 900 A-104 Rye 2000
Borrower Table A-105 Downtown 200

customer_name loan_number
Adams L-1 Depositor Table

Jackson L-2 customer_name account_number

Smith L-4 Jackson A-101
Jose L-5 Smith A-103
Adams A-102
Jack A-104
Jose A-105
 Borrower Table
Set Operations: customer_name loan_number

The Union Operation Adams

Jackson
L-1
L-2
Find all customers having a loan, an account, or both at Smith L-4
the bank
Jose L-5
(select customer-name
from depositor)
union
(select customer-name Depositor Table

from borrower)
customer_name account_number
Jackson A-101
we want to retain all duplicates, we must write union all Smith A-103
in place of union: Adams A-102
(select customer-name
from depositor) Jack A-104
union all Jose A-105
(select customer-name
from borrower)
 Borrower Table

The Intersect Operation customer_name

Adams
loan_number
L-1
Jackson L-2
Find all customers who have both a loan and an account at the bank. Smith L-4
(select distinct customer-name Jose L-5
from depositor)
intersect
(select distinct customer-name
Depositor Table
from borrower)
customer_name account_number
to retain all duplicates, we must write intersect all in Jackson A-101
place of intersect: Smith A-103
Adams A-102
Jack A-104
Jose A-105
 Borrower Table

The Except Operation customer_name

Adams
loan_number
L-1
Jackson L-2
Find all customers who have an account but no loan at the bank Smith L-4
(select distinct customer-name Jose L-5
from depositor)
except
(select customer-name
Depositor Table
from borrower)

customer_name account_number
Jackson A-101
Smith A-103
Adams A-102
Jack A-104
Jose A-105
Null Values
Find all loan numbers that appear in the loan relation with null values for amount

select loan-number
The predicate is not null tests for the absence of a null
from loan
value
where amount is null

The result of an arithmetic expression (involving, for

example +, −, ∗ or /) is null if any of the input values is
null
 Loan Table Account Table
loan_number branch_name amount
L-1 Downtown 1000 account_number branch_name balance
L-2 Perryridge 2000 A-101 Perryridge 1000
L-3 Mianus 500 A-102 Downtown 200
L-4 Perryridge 1200 A-103 Perryridge 3000
L-5 Perryridge 900 A-104 Rye 2000
Borrower Table A-105 Downtown 200

customer_name loan_number
Adams L-1 Depositor Table

Jackson L-2 customer_name account_number

Smith L-4 Jackson A-101
Jose L-5 Smith A-103
Adams A-102
Jack A-104
Jose A-105
Nested Subqueries
• Find all customers who have both a loan and an account at the bank.

select distinct customer-name

from borrower
where customer-name in (select customer-name
from depositor)
Nested Subqueries
Find all customers who have both an account and a loan at the Perryridge branch

select distinct customer-name

from borrower, loan
where [Link]-number = [Link]-number and
branch-name = ’Perryridge’ and
(branch-name, customer-name) in (select branch-name, customer-name
from depositor, account
where [Link]-number = [Link]-number)
SELECT customer_name
FROM borrower
WHERE loan_number IN ( SELECT loan_number
FROM loan
WHERE branch_name = 'Perryridge'
AND amount = (
SELECT MAX(amount)
FROM loan
WHERE branch_name = 'Perryridge'
)
);
 Find the names of all branches that have assets Branch Table
greater than those of at least one branch located
in Brooklyn. branch_name branch_city assets
Downtown Brooklyn 500000
select distinct [Link]-name Red Bank Brooklyn 600000
from branch as T, branch as S Mianus Mianus 800000
where [Link] > [Link] and [Link]-city = ’Brooklyn’ Perryridge Horseneck 700000
Brighton Brooklyn 400000
Central Bronx 750000
 “greater than at least one” is represented in SQL by > some

Find the names of all branches that have assets Branch Table
greater than those of at least one branch located
in Brooklyn. branch_name branch_city assets
select branch-name Downtown Brooklyn 500000
from branch Red Bank Brooklyn 600000
where assets > some (select assets
Mianus Mianus 800000
from branch
where branch-city = ’Brooklyn’) Perryridge Horseneck 700000
Brighton Brooklyn 400000
Central Bronx 750000

SQL also allows < some, <= some, >= some, = some, and<> some comparisons.
 The construct > all corresponds to the phrase “greater than all.”

Find the names of all branches that have assets Branch Table
greater than all branches located in Brooklyn.
branch_name branch_city assets
select branch-name Downtown Brooklyn 500000
from branch Red Bank Brooklyn 600000
where assets > all (select assets
Mianus Mianus 800000
from branch
where branch-city = ’Brooklyn’) Perryridge Horseneck 700000
Brighton Brooklyn 400000
Central Bronx 750000

SQL also allows < all, <= all, >= all, = all, and <> all comparisons.
As an exercise, verify that <> all is identical to not in.
 Loan Table Account Table
loan_number branch_name amount
L-1 Downtown 1000 account_number branch_name balance
L-2 Perryridge 2000 A-101 Perryridge 1000
L-3 Mianus 500 A-102 Downtown 200
L-4 Perryridge 1200 A-103 Perryridge 3000
L-5 Perryridge 900 A-104 Rye 2000
Borrower Table A-105 Downtown 200

customer_name loan_number
Adams L-1 Depositor Table

Jackson L-2 customer_name account_number

Smith L-4 Jackson A-101
Jose L-5 Smith A-103
Adams A-102
Jack A-104
Jose A-105
 find the maximum across all branches of the total balance at each branch.

Account Table
select max(tot-balance)
account_number branch_name balance
from (select branch-name, sum(balance) as tot-balance
from account A-101 Perryridge 1000
group by branch-name) A-102 Downtown 200
A-103 Perryridge 3000
A-104 Rye 2000
A-105 Downtown 2000
• Find the branch that has the highest average balance.

Account Table

select branch-name account_number branch_name balance

from account A-101 Perryridge 1000
group by branch-name A-102 Downtown 200
having avg (balance) >= all (select avg (balance)
from account A-103 Perryridge 3000
group by branch-name) A-104 Rye 2000
A-105 Downtown 2000
 Complex Queries: Derived Relations
Find the average account balance of those branches where the average account balance is greater than $1200

Account Table
STEP 1:
(select branch-name, avg (balance)
account_number branch_name balance
avg_sal
from account A-101 Perryridge 1000
group by branch-name) A-102 Downtown 200
as branch-avg (branch-name, avg-
A-103 Perryridge 3000
balance)
A-104 Rye 2000
FINAL Query
A-105 Downtown 2000
select avg(avg-balance)
from (select branch-name, avg (balance)
avg_sal
from account
group by branch-name)
as where avg-balance > 1200
Complex Queries: with Clause
selects accounts with the maximum balance.

Account Table

with max-balance (value) as account_number branch_name balance

select max(balance) A-101 Perryridge 1000
from account A-102 Downtown 200
select account-number A-103 Perryridge 3000
from account, max-balance A-104 Rye 2000
where [Link] = [Link] A-105 Downtown 2000
 find all branches where the total account deposit is less than the average of the total account deposits
at all branches

WITH branch_total (branch_name, value) AS (

SELECT branch_name, SUM(balance) Account Table
FROM account
GROUP BY branch_name account_number branch_name balance
),
branch_total_avg (value) AS ( A-101 Perryridge 1000
SELECT AVG(value) A-102 Downtown 200
FROM branch_total
A-103 Perryridge 3000
)
SELECT branch_name A-104 Rye 2000
FROM branch_total, branch_total_avg A-105 Downtown 2000
WHERE branch_total.value >= branch_total_avg.value;
View
A view is a named SQL query that appears as a table but does not physically store the data. Views are used
to simplify complex queries, provide security by hiding certain columns, or present data in a particular
format.
create view loan-branch as select branch-name, loan-number from loan
Join Conditions and Join Types
Join Conditions:
• Equijoins use the equality operator (=). You’ve already seen examples of equijoins.
• Non-equijoins use an operator other than the equality operator, such as <, >, BETWEEN, and so on.

Three different types of joins:

• Inner joins return a row only when the columns in the join contain values that satisfy the join condition. This
means that if a row has a null value in one of the columns in the join condition, that row isn’t returned. The
examples you’ve seen so far have been inner joins.
• Outer joins return a row even when one of the columns in the join condition contains a null value.
• Self joins return rows joined on the same table.
 Inner Join

SQL QUERY:
SELECT * FROM loan INNER JOIN borrower ON loan.loan_number = borrower.loan_number;
Result:
 Left Join

SQL QUERY:
SELECT * FROM loan LEFT JOIN borrower ON loan.loan_number = borrower.loan_number;

Result:
loan_number branch_name amount customer_name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 NULL
 Right Join

SQL QUERY:
SELECT * FROM loan RIGHT JOIN borrower ON loan.loan_number = borrower.loan_number;

Result:
loan_number branch_name amount customer_name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
NULL NULL NULL Hayes
 Full Outer Join

SQL QUERY:
SELECT * FROM loan Full outer JOIN borrower ON loan.loan_number = borrower.loan_number;

Result:
loan_number branch_name amount customer_name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 NULL
NULL NULL NULL Hayes
Cross Join
SQL QUERY:
SELECT * FROM loan cross JOIN borrower ;

Result:
customer_na
loan_number branch_name amount
me
L-170 Downtown 3000 Jones
L-170 Downtown 3000 Smith
L-170 Downtown 3000 Hayes
L-230 Redwood 4000 Jones
L-230 Redwood 4000 Smith
L-230 Redwood 4000 Hayes
L-260 Perryridge 1700 Jones
L-260 Perryridge 1700 Smith
L-260 Perryridge 1700 Hayes
 Practice Excercise
1) List all students with their enrolled course names.
2) Display professor name and course names
3) show all students and their grades(even if not enrolled)
University Schema 4) Find the names of students enrolled in 'Database
Systems
Table Columns 5) Students who got highest grade in any course
Students student_id, student_name, major 6) Students who are enrolled in a course OR are majoring
in 'CS’
Courses course_id, course_name, department 7) Students who are majoring in 'CS' but NOT enrolled in
Enrollments student_id, course_id, grade any course
Professors prof_id, prof_name, department 8) Students enrolled in both Courses CS101 and CS102
9) List students who are enrolled in every course taught by
Teaches prof_id, course_id Prof. "John Smith“
10) Find top 3 students by GPA
11) List all courses with the average grade, sorted by
highest average
12) Find professors who teach in departments other than
their own
13) Display all students who have the same major as at
least one other student
 Practice Excercise
University Schema

Table Columns
1) List all students with their enrolled course names.
Students student_id, student_name, major
Courses course_id, course_name, department SELECT s.student_name, c.course_name
Enrollments student_id, course_id, grade FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
Professors prof_id, prof_name, department JOIN Courses c ON e.course_id = c.course_id;
Teaches prof_id, course_id
 Practice Excercise
University Schema

Table Columns
2) Display professor name and course names
Students student_id, student_name, major
Courses course_id, course_name, department SELECT p.prof_name, c.course_name
Enrollments student_id, course_id, grade FROM Professors p
JOIN Teaches t ON p.prof_id = t.prof_id
Professors prof_id, prof_name, department JOIN Courses c ON t.course_id = c.course_id;
Teaches prof_id, course_id
 Practice Excercise
3) show all students and their grades(even if not enrolled)

University Schema SELECT s.student_name, [Link]

FROM Students s
Table Columns LEFT JOIN Enrollments e ON s.student_id = e.student_id;
Students student_id, student_name, major
Courses course_id, course_name, department
Enrollments student_id, course_id, grade
Professors prof_id, prof_name, department
Teaches prof_id, course_id
 Practice Excercise
4) Find the names of students enrolled in 'Database
Systems
University Schema
SELECT student_name
Table Columns FROM Students
Students student_id, student_name, major WHERE student_id IN (
SELECT student_id
Courses course_id, course_name, department FROM Enrollments
Enrollments student_id, course_id, grade WHERE course_id = (
Professors prof_id, prof_name, department SELECT course_id
FROM Courses
Teaches prof_id, course_id WHERE course_name = 'Database Systems'
)
);
 Practice Excercise
Students who got highest grade

University Schema SELECT student_name

FROM Students
Table Columns WHERE student_id IN (
Students student_id, student_name, major SELECT student_id
FROM Enrollments
Courses course_id, course_name, department WHERE grade = (
Enrollments student_id, course_id, grade SELECT MAX(grade)
Professors prof_id, prof_name, department FROM Enrollments
)
Teaches prof_id, course_id );
 Practice Excercise
6) Students who are enrolled in a course OR are majoring
in 'CS’

University Schema SELECT student_id FROM Enrollments

UNION
Table Columns SELECT student_id FROM Students WHERE major = 'CS';
Students student_id, student_name, major
Courses course_id, course_name, department
Enrollments student_id, course_id, grade
Professors prof_id, prof_name, department
Teaches prof_id, course_id
 Practice Excercise
7) Students who are majoring in 'CS' but NOT enrolled in any
University Schema course

Table Columns
Students student_id, student_name, major SELECT student_id
FROM Students
Courses course_id, course_name, department
WHERE major = 'CS'
Enrollments student_id, course_id, grade MINUS
Professors prof_id, prof_name, department SELECT student_id FROM Enrollments;
Teaches prof_id, course_id
 Practice Excercise
8) Students enrolled in both Courses CS101 and CS102
University Schema
SELECT student_id FROM Enrollments WHERE course_id =
Table Columns 'CS101'
Students student_id, student_name, major INTERSECT
SELECT student_id FROM Enrollments WHERE course_id =
Courses course_id, course_name, department
'CS201';
Enrollments student_id, course_id, grade
Professors prof_id, prof_name, department
Teaches prof_id, course_id
 Practice Excercise
10) Find top 3 students by GPA
University Schema

Table Columns SELECT *

Students student_id, student_name, major FROM (
SELECT student_name, AVG(grade) AS gpa
Courses course_id, course_name, department
FROM Students s
Enrollments student_id, course_id, grade JOIN Enrollments e ON s.student_id = e.student_id
Professors prof_id, prof_name, department GROUP BY student_name
ORDER BY gpa DESC
Teaches prof_id, course_id
)
WHERE ROWNUM <= 3;
 Practice Excercise
1) List all students with their enrolled course names.
2) Find all professors teaching a course
3) show all students and their grades(even if not enrolled)
University Schema 4) Find the names of students enrolled in 'Database
Systems
Table Columns 5) Students who got highest grade in any course
Students student_id, student_name, major 6) Students who are enrolled in a course OR are majoring
in 'CS’
Courses course_id, course_name, department 7) Students who are majoring in 'CS' but NOT enrolled in
Enrollments student_id, course_id, grade any course
Professors prof_id, prof_name, department 8) Students enrolled in both Courses CS101 and CS102
9) List students who are enrolled in every course taught by
Teaches prof_id, course_id Prof. "John Smith“
10) Find top 3 students by GPA
11) List all courses with the average grade, sorted by
highest average
12) Find professors who teach in departments other than
their own
13) Display all students who have the same major as at
least one other student
Display employee details along with salary grade

Select * from emp;

Select * from salary_grade;

select [Link], [Link], sg.salary_grade from emp e, salary_grade sg

where [Link] between sg.low_salary and sg.high_salary order by
salary_grade;
Nested Queries Department Table Student Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
105 Eva 23 1
Find students who are not enrolled in any
course. 106 John 21 1
Course Table Enroll Table
credit sid cid grade
SELECT sname cid cname dept_id
FROM Students s 101 201 85
WHERE sid NOT IN (SELECT sid FROM 201 DBMS 4 1 101 202 78
Enrollments); 202 Algorithms 3 1 102 201 90
203 Circuits 4 2 103 203 70
Thermodyn 104 204 75
204 3 3
amics
105 201 88
205 AI 3 1
105 205 92
Nested Queries Department Table Student Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
105 Eva 23 1
Find students who scored above the average
grade in DBMS.
Course Table Enroll Table
SELECT sname
credit sid cid grade
FROM Students cid cname dept_id
WHERE sid IN ( s 101 201 85
SELECT sid FROM Enrollments 201 DBMS 4 1 101 202 78
WHERE cid = (SELECT cid FROM Courses 202 Algorithms 3 1 102 201 90
WHERE cname = 'DBMS')
AND grade > (SELECT AVG(grade) FROM 203 Circuits 4 2 103 203 70
Enrollments WHERE cid = Thermodyn 104 204 75
204 3 3
(SELECT cid FROM Courses WHERE amics
105 201 88
cname = 'DBMS')) 205 AI 3 1
); 105 205 92
Find students who scored above the average grade in DBMS.

SELECT sname
FROM Students
WHERE sid IN (
SELECT sid FROM Enrollments
WHERE cid = (SELECT cid FROM Courses WHERE cname = 'DBMS')
AND grade > (SELECT AVG(grade) FROM Enrollments WHERE cid =
(SELECT cid FROM Courses WHERE cname = 'DBMS'))
);

R1-> (SELECT cid FROM Courses WHERE cname = 'DBMS’)

R2-> (SELECT AVG(grade) FROM Enrollments WHERE cid = R1)
R3-> SELECT sid FROM Enrollments
WHERE cid =R1 AND grade > R2
R4 -> SELECT sname FROM Students WHERE sid IN (R3);
Nested Queries Department Table Students Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
105 Eva 23 1
Find students enrolled in courses by CSE
department. Enrollments Table
Courses Table sid cid grade
Select sname from student where sid in
credit 101 201 85
(Select sid from enroll where cid in cid cname dept_id
(Select cid from course where dept_id in s 101 202 78
( Select dept_id from department 201 DBMS 4 1 102 201 90
where dept_name=‘CSE’) 202 Algorithms 3 1 103 203 70
)
); 203 Circuits 4 2 104 204 75
Thermodyn 105 201 88
204 3 3
amics
105 205 92
205 AI 3 1
101 205 88
Nested Queries Department Table Students Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
105 Eva 23 1
Find departments where all students are
older than 20.
Courses Table Enrollments Table
Select dept_name from department
credit sid cid grade
where dept_id not in cid cname dept_id
(select dept_id from students where s 101 201 85
age<=20) 201 DBMS 4 1 101 202 78
202 Algorithms 3 1 102 201 90
select deptname from department d 203 Circuits 4 2 103 203 70
where 20 < all ( select [Link] Thermodyn 104 204 75
204 3 3
from students s where s.dept_id = amics
105 201 88
d.dept_id ); 205 AI 3 1
105 205 92
Nested Queries Department Table Students Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
105 Eva 23 1
Find students who have enrolled in more
courses than Bob.
Courses Table Enrollments Table
credit sid cid grade
cid cname dept_id
Select sname from students where sid in s 101 201 85
(Select sid from enrollments e group by 201 DBMS 4 1 101 202 78
sid having count(cid)> (Select count(*) 202 Algorithms 3 1 102 201 90
from enrollments where sid = (Select sid
from students where sname =‘Bob’) ); 203 Circuits 4 2 103 203 70
Thermodyn 104 204 75
204 3 3
amics
105 201 88
205 AI 3 1
105 205 92
Nested Queries Department Table Students Table
sid sname age dept_id
dept_id dept_name
Students(sid, sname, age, dept_id) 101 Alice 21 1
Department(dept_id, dept_name) 1 CSE 102 Bob 19 1
Courses(cid, cname, credits, dept_id) 2 ECE 103 Charlie 22 2
Enrollments(sid, cid, grade) 3 MECH 104 David 20 3
Find the student(s) who scored the
105 Eva 23 1
second highest grade in DBMS.

Courses Table Enrollments Table

credit sid cid grade
cid cname dept_id
s 101 201 85
201 DBMS 4 1 101 202 78
202 Algorithms 3 1 102 201 90
203 Circuits 4 2 103 203 70
Thermodyn 104 204 75
204 3 3
amics
105 201 88
205 AI 3 1
105 205 92
select sname from students
where sid in (
select sid from enrollments where cid = (select cid from courses where
cname = 'dbms’) and grade = (
select max(grade)
from enrollments
where cid = (select cid from courses where cname = 'dbms')
and grade < (select max(grade) from enrollments
where cid = (select cid from courses where cname = 'dbms'))
)
);
Nested Queries

Display student who got the lowest marks in each course

select student_id, course_name, marks
from course
where (course_name, marks) in
(select course_name, min(marks) from course group by course_name);
Nested Queries

Find students who scored more than the average marks of their own department.
select c1.student_id, [Link], c1.course_name, [Link]
from course c1
where [Link] >
(select avg([Link])
from course c2
where [Link] = [Link]);