Q1)
SELECT i.name, t.course_id, t.semester, t.year
FROM instructor i
JOIN teaches t ON i.T_ID = t.T_ID
ORDER BY t.semester, t.year;
CREATE VIEW student_details AS
SELECT * FROM student;
SELECT * FROM student_details;
ALTER TABLE student CHANGE dept_name department_name VARCHAR(255);
DELETE FROM student
WHERE department_name IS NULL;
Q2)
SELECT name
FROM instructor
WHERE salary > 25000;
UPDATE instructor
SET dept_name = 'IT'
WHERE dept_name = 'CSE';
CREATE INDEX idx_dept_name ON course(dept_name);
SELECT dept_name, title
FROM course
ORDER BY dept_name;
CREATE VIEW instructor_course_details AS
SELECT i.T_ID, i.name AS instructor_name, c.course_id, c.title, t.sec_id, t.semester, t.year
FROM instructor i
JOIN teaches t ON i.T_ID = t.T_ID
JOIN course c ON t.course_id = c.course_id;
SELECT * FROM instructor_course_details;
Q3)
SELECT name
FROM instructor
WHERE salary BETWEEN 30000 AND 60000
ORDER BY salary DESC;
SELECT dept_name,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM instructor
GROUP BY dept_name;
SELECT i.name, i.dept_name, i.salary
FROM instructor i
JOIN teaches t ON i.T_ID = t.T_ID
WHERE t.course_id = 101;
SELECT name
FROM instructor
WHERE salary > 60000;
Q4)
SELECT dept_name, AVG(salary) AS average_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;
UPDATE instructor
SET salary = salary * 1.10
WHERE dept_name = 'Computer';
SELECT name
FROM instructor
WHERE name NOT IN ('Amol', 'Amit');
SELECT name
FROM student
WHERE name LIKE '%am%';
SELECT s.name
FROM student s
JOIN course c ON s.dept_name = c.dept_name
WHERE s.dept_name = 'Computer' AND c.title = 'DBMS';
Q5)
SELECT COUNT(DISTINCT T_ID) AS total_instructors
FROM teaches
WHERE semester = 'First' AND year = 2010;
SELECT name
FROM instructor
WHERE dept_name = 'Computer' AND name LIKE '%shree%';
SELECT name
FROM instructor
WHERE salary > (
SELECT AVG(salary)
FROM instructor
WHERE dept_name = 'Civil'
);
SELECT DISTINCT course_id
FROM teaches
WHERE (semester = 'Fifth' AND year = 2014)
OR (semester = 'First' AND year = 2010);
Q6)
UPDATE Company
SET city = 'Pune'
WHERE cname = 'ABC';
UPDATE works
SET sal =
CASE
WHEN sal > 20000 THEN sal * 1.03
ELSE sal * 1.10
END
WHERE cname = 'Mbank' AND emp_id IN (SELECT mgr_id FROM Manager);
SELECT e.ename
FROM Emp e
JOIN works w ON e.emp_id = w.emp_id
JOIN Company c ON w.c_id = c.c_id
WHERE c.cname = 'Bosch' AND c.city = 'Pune';
DELETE FROM works
WHERE cname = 'SBC Company' AND sal > 50000;
Q7)
SELECT *
FROM Duty_alloc
WHERE e_no = 123 AND shift = 'first' AND month = 'April' AND year = 2003;
SELECT e_name
FROM Empl
WHERE pay_rate >= (
SELECT pay_rate
FROM Empl
WHERE e_name = 'Sachin'
);
CREATE VIEW post_salary_stats AS
SELECT post,
MIN(pay_rate) AS min_salary,
MAX(pay_rate) AS max_salary,
AVG(pay_rate) AS avg_salary
FROM Empl
GROUP BY post;
SELECT * FROM post_salary_stats;
SELECT da.shift, COUNT(DISTINCT da.e_no) AS employee_count
FROM Duty_alloc da
JOIN Empl e ON da.e_no = e.e_no
WHERE e.post = 'manager'
GROUP BY da.shift;
