SQL Lab Exercises (1 to 7)
-- 1. Student Table with Marks and Total
CREATE TABLE student (
name VARCHAR(50),
register_number INT PRIMARY KEY,
department VARCHAR(50),
mark1 INT,
mark2 INT,
mark3 INT,
mark4 INT,
mark5 INT,
total INT
);
-- (a) Insert few records
INSERT INTO student VALUES ('Ajay', 101, 'CSE', 80, 85, 78, 90, 88, 0);
INSERT INTO student VALUES ('Vimal', 102, 'ECE', 70, 60, 80, 75, 65, 0);
-- (b) Display all records
SELECT * FROM student;
-- (c) Calculate total marks
UPDATE student SET total = mark1 + mark2 + mark3 + mark4 + mark5;
-- (d) Display name, register number, total
SELECT name, register_number, total FROM student;
-- 2. Modify, Delete
-- (b) Update name
UPDATE student SET name = 'Vignesh' WHERE register_number = 211278019;
-- (c) Delete record
DELETE FROM student WHERE register_number = 211278005;
-- 3. Table with Integrity Constraints
CREATE TABLE student_details (
name VARCHAR(50) CHECK (name = UPPER(name)),
roll_number INT CHECK (roll_number > 0),
gender VARCHAR(10) CHECK (gender IN ('Male', 'Female', 'Transgend')),
age INT NOT NULL,
mobile_number BIGINT
);
-- 4. student_master Table and Selects
CREATE TABLE student_master (
name VARCHAR(50),
regno INT,
dept VARCHAR(50),
year_of_joining INT
);
-- (a) SELECT *
SQL Lab Exercises (1 to 7)
SELECT * FROM student_master;
-- (b) name only
SELECT name FROM student_master;
-- (c) DISTINCT
SELECT DISTINCT * FROM student_master;
-- (d) WHERE clause
SELECT * FROM student_master WHERE dept = 'Computer Science';
-- (e) Sort by name
SELECT * FROM student_master ORDER BY name;
-- 5. sales_order_details Table
CREATE TABLE sales_order_details (
s_order_no INT PRIMARY KEY,
product_no VARCHAR(20),
description VARCHAR(100),
qty_ordered INT,
qty_disp INT,
product_rate DECIMAL(10,2),
profit_percent DECIMAL(5,2),
sell_price DECIMAL(10,2),
supplier_name VARCHAR(50)
);
-- (a) Compute 50% and 150% of sell_price
SELECT sell_price, sell_price * 0.5 AS half_price, sell_price * 1.5 AS one_half_price FROM
sales_order_details;
-- (b) profit_percent NOT between 10 and 20
SELECT product_no, profit_percent, sell_price FROM sales_order_details WHERE profit_percent NOT
BETWEEN 10 AND 20;
-- (c) profit_percent NOT between 20 and 30
SELECT product_no, description, profit_percent, sell_price FROM sales_order_details WHERE
profit_percent NOT BETWEEN 20 AND 30;
-- (d) supplier name second letter 'r' or 'h'
SELECT supplier_name, product_no FROM sales_order_details WHERE supplier_name LIKE '_r%' OR
supplier_name LIKE '_h%';
-- 6. Employee table with self-reference
CREATE TABLE employee (
employee_number INT PRIMARY KEY,
name VARCHAR(50),
job VARCHAR(50),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employee(employee_number)
);
-- (a) Insert few records
INSERT INTO employee VALUES (1, 'Ravi', 'Manager', NULL);
INSERT INTO employee VALUES (2, 'Kumar', 'Developer', 1);
SQL Lab Exercises (1 to 7)
INSERT INTO employee VALUES (3, 'Anu', 'Tester', 1);
-- (b) Display all
SELECT * FROM employee;
-- (c) Employees under manager
SELECT * FROM employee WHERE manager_id = 1;
-- 7. Employee salary filters
CREATE TABLE emp_salary (
employee_number INT PRIMARY KEY,
employee_name VARCHAR(50),
department_number INT,
job VARCHAR(50),
salary DECIMAL(10,2)
);
-- (a) salary > 20000
SELECT employee_name, salary FROM emp_salary WHERE salary > 20000;
-- (b) name and dept for emp number
SELECT employee_name, department_number FROM emp_salary WHERE employee_number = 2;
-- (c) salary NOT in 15000-30000
SELECT employee_name, salary FROM emp_salary WHERE salary NOT BETWEEN 15000 AND 30000;
-- Continued in next cell...
