DBMS Notes selection projection aggregate

1
DATABASE MANAGEMENT
SYSTEM
Unit - III

2
SELECTION & PROJECTION
Retrieve all columns and rows from the Student table.
Retrieve only specific columns from the Student table.
Retrieve students who are in the specific department.
Retrieve all students sorted by age in ascending order.
SELECT * FROM student;
SELECT student_id, first_name, last_name FROM student;
SELECT * FROM student WHERE department = 'Computer Science';
SELECT * FROM student ORDER BY age ASC;

Retrieve students sorted by (ORDER BY) grade in descending order
Retrieve only the first 4 rows (ROWNUM) from the table
Retrieve students who are in the Computer Science department
AND have a grade of A
Retrieve students whose first_name starts with C (LIKE ’C%’)
Retrieve students who are IN either Physics or Chemistry
departments
SELECT * FROM student ORDER BY grade DESC;
select * from student where ROWNUM <= 4
SELECT * FROM student WHERE department = 'Computer Science' AND
grade = 'A';
SELECT * FROM student WHERE first_name LIKE 'C%';
SELECT * FROM student WHERE department IN ('Physics', 'Chemistry');

Retrieve students whose age is BETWEEN 21 AND23
Retrieve the total number of students (COUNT)
Retrieve the average age of students (AVG)
Retrieve the highest age in the table (MAX)
Retrieve the number (COUNT) of students in each department,
GROUP BY Department
SELECT * FROM student WHERE age BETWEEN 21 AND 23;
SELECT COUNT(*) AS total_students FROM student;
SELECT AVG(age) AS average_age FROM student;
SELECT MAX(age) AS highest_age FROM student;
SELECT department, COUNT(*) AS student_count FROM
student GROUP BY department;

Retrieve the number (COUNT) of students in each
department, GROUP BY Department
SELECT department, COUNT(*) AS student_count
FROM student GROUP BY department;

Retrieve departments with more than 2 students.
SELECT department, COUNT(*) AS student_count FROM
student GROUP BY department HAVING COUNT(*) > 2;

Retrieve unique (DISTINCT) departments from the
student table.
SELECT DISTINCT department FROM student;

Retrieve students who are older than the average age
SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);

Retrieve students from two departments and combine results.
SELECT * FROM student WHERE department = 'Computer Science'
UNION
SELECT * FROM student WHERE department = 'Mathematics';

Retrieve students with a custom column indicating if they are a senior
(age >= 21) or junior (age < 21) as status , order by status. (CASE END)
SELECT student_id, first_name, last_name, age,
CASE
WHEN age >= 21 THEN 'Senior'
ELSE 'Junior'
END AS student_status
FROM student ORDER BY student_status;

Retrieve students who do not have a grade assigned.
SELECT * FROM student WHERE grade IS NULL;

12
SQL: Arithmetic and Logical Operations
• Arithmetic Operations:
▪ Multiplication (*),
▪ Addition (+),
▪ Subtraction (-),
▪ Division (/)
• Logical Operations:
▪ AND,
▪ OR,
▪ NOT

13
SQL: Arithmetic and Logical Operations
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
department VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2)
);
INSERT INTO employee (employee_id, first_name, last_name, email,
department, hire_date, salary)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', 'Sales', '15-01-2020',
55000.00),
(2, 'Jane', 'Smith', 'jane.smith@example.com', 'Engineering', '16-01-
2020', 75000.00),
(3, 'Sam', 'Brown', 'sam.brown@example.com', 'HR', '17-01-2020',
48000.00),
(4, 'Emily', 'Jones', 'emily.jones@example.com', 'Marketing', '15-01-
2021', 60000.00),
(5, 'Michael', 'Taylor', 'michael.taylor@example.com', 'Sales', '16-01-
2021', 53000.00),
(6, 'Linda', 'Lee', 'linda.lee@example.com', 'Engineering', '16-01-
2021', 78000.00),
(7, 'Chris', 'Davis', 'chris.davis@example.com', 'Finance', '17-01-2021',
90000.00),
(8, 'Anna', 'Martinez', 'anna.martinez@example.com', 'HR', '15-01-
2022', 52000.00),
(9, 'David', 'Wilson', 'david.wilson@example.com', 'Marketing', '16-
01-2022', 62000.00),
(10, 'Sophia', 'Miller', 'sophia.miller@example.com', 'Finance', '17-01-
2022', 95000.00);

Calculate Annual Salary of the employees
SELECT first_name, last_name, salary, (salary * 12)
AS annual_salary
FROM employee;
Arithmetic Operation
Multiplication (*)

Calculate the salary after applying the bonus (10%)
SELECT first_name, last_name, salary,
(salary + (salary * 0.10))
AS salary_after_bonus
FROM employee;
Addition (+)

Calculate the salary after deductions (5%)
(salary - (salary * 0.05))
AS salary_after_deduction
FROM employee;
Subtraction (-)

Calculate the salary of the employees per day
ROUND((salary / 30),2)
AS salary_per_day
FROM employee;
Division (/)

Find employees who work either in the 'Sales' or
'Marketing' department
SELECT first_name, last_name, department
FROM employee
WHERE department = 'Sales' OR department = 'Marketing';
Logical Operation
OR

Find employees who work in the 'Engineering'
department and have a salary greater than 60,000
SELECT first_name, last_name, salary, department
FROM employee
WHERE salary > 60000 AND department = 'Engineering';
Logical Operation
AND

Find employees who do not work in the 'HR'
department
SELECT first_name, last_name, department
FROM employee
WHERE NOT department = 'HR';
Logical Operation
NOT

22
Lab Program 6
• Develop a program that includes the features NESTED IF, CASE.
The program can be extended using the NULLIF and COALESCE
functions.
NESTED IF
CASE
NULLIF
COALESCE

23
Lab Program 6
• Accept the marks and display the grade as per the
following
▪ If Marks are >= 90, then Grade is A.
▪ ElsIf Marks are >= to 80, then Check if Marks >= 85, then Grade
is B+, else Grade is B.
▪ ElsIf Marks are >= to 70, then Grade is C,
▪ else Grade is D

Lab Program 6
DECLARE
BEGIN
END
/
v_grade CHAR(2);
v_marks NUMBER;
v_marks := &new_marks;
IF v_marks >= 90 THEN v_grade := 'A';
ELSIF v_marks >= 80 THEN
IF v_marks >= 85 THEN v_grade := 'B+';
ELSE v_grade := 'B';
END IF;
ELSIF v_marks >= 70 THEN v_grade := 'C';
ELSE v_grade := 'D';
ENDIF
DBMS_OUTPUT.PUT_LINE('The grade is: ' || v_grade);
NESTED IF

25
Lab Program 6: CASE
• Accept the Grade and display as per the following
▪ A – Outstanding
▪ B – Excellent
▪ C – Very Good
▪ D – Average
▪ F - Poor

Lab Program 6: CASE
DECLARE
BEGIN
END
/
grd CHAR(1);
grd := '&new_grd';
CASE
END CASE;
grd
WHEN 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');
WHEN 'B' THEN dbms_output.Put_line('Your Grade is: Excellent');
WHEN 'C' THEN dbms_output.Put_line('Your Grade is: Very Good');
WHEN 'D' THEN dbms_output. Put_line('Your Grade is: Average');
WHEN 'F' THEN dbms_output.Put_line('Your Grade is: Poor');
ELSE dbms_output.Put_line('No such grade in the list.');

Lab Program 6: NULLIF, COALESCE
SQL COALESCE function is used to handle NULL values
in a database query by returning the first non-NULL value
from a list of expressions or column values
SQL COALESCE function deals with NULL values in a query
by substituting them with a default value or selecting the first
non-NULL value from a list of option
NULLIF compares expr1 and expr2. If they are equal, then
the function returns null. If they are not equal, then the
function returns expr1

CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary DECIMAL(10, 2),
department VARCHAR2(50),
hire_date DATE
);
Lab Program 6: NULLIF, COALESCE
INSERT INTO employee (employee_id, first_name, last_name, salary, department, hire_date)
VALUES (1, 'Ram', 'Charan', 50000, 'HR', TO_DATE('2020-01-15', 'YYYY-MM-DD'));
VALUES (2, 'Pawan', 'Smith', 16000, 'Finance', TO_DATE('2018-03-10', 'YYYY-MM-DD'));
VALUES (3, 'Ram', 'Shyam', 7000, '', TO_DATE('2025-02-09', 'YYYY-MM-DD'));
INSERT INTO employee (employee_id, first_name, last_name, salary, department, hire_date) VALUES (4, 'Bob', 'Brown', 8000, 'HR',
TO_DATE('2021-11-05', 'YYYY-MM-DD'));
INSERT INTO employee (employee_id, first_name, last_name, salary, department, hire_date) VALUES (5, 'Charlie', 'Davis', 9000, 'Finance',
TO_DATE('2017-02-17', 'YYYY-MM-DD'));
VALUES (6, 'David', 'Garcia', 10000, 'Engineering', TO_DATE('2025-02-09', 'YYYY-MM-DD'));
VALUES (7, 'Emma', 'Martinez', 5500, 'Marketing', TO_DATE('09-02-2025', 'DD-MM-YY'));
VALUES (8, 'Frank', 'Hernandez', 6500, 'HR', TO_DATE('2022-01-10', 'YYYY-MM-DD'));
VALUES (9, 'Grace', 'Lopez', 7500, 'Finance', TO_DATE('2019-12-05', 'YYYY-MM-DD'));
VALUES (10, 'Brahmanandam', 'Garu', 9500, '', TO_DATE('2016-07-30', 'YYYY-MM-DD'));
VALUES (11, 'Ram', 'Pay','' ,'Marketing', TO_DATE('2019-07-30', 'YYYY-MM-DD'));
VALUES (12, 'Hare', 'Krishna', 19500, 'HR', NULL);
VALUES (13, 'Rama', 'Krishna','','Sales', TO_DATE('2021-07-30', 'YYYY-MM-DD'));

Lab Program 6: COALESCE
Replace all NULL values in the department column with
default department ie., CSE using COALESCE
SELECT employee_id, department, COALESCE(department,'CSE')
AS UpdatedDept
FROM employee;
Replace all NULL values in the salary column with 1000 using
COALESCE
SELECT employee_id, salary, COALESCE(salary,1000) AS UpdatedSal
FROM employee;

Lab Program 6: NULLIF
If salary is equal to 50000 then assign with NULL
SELECT employee_id, salary, NULLIF(salary,50000)
AS UpdatedSal
FROM employee;
Marketing Dept is closed;
If department is Marketing, then assign with NULL
SELECT employee_id, department,
NULLIF(department,’Marketing’) AS UpdatedDept
FROM employee;

31
Unit III
• SQL: Basic SQL querying (select and project) using where clause,
arithmetic & logical operations,
• SQL functions (Date and Time, Numeric, String conversion).
• Creating tables with relationship, implementation of key and integrity
constraints, grouping, aggregation, ordering,
•nested queries, sub queries,
•implementation of different types of joins,
•relational set operations

DBMS Notes selection projection aggregate

More Related Content

What's hot

Similar to DBMS Notes selection projection aggregate

More from Sreedhar Chowdam

Recently uploaded

DBMS Notes selection projection aggregate