MANISH N

CREATE TABLE INSTRUCTOR ( InstructID INT PRIMARY KEY, InstructNAME VARCHAR(25), DEP VARCHAR(25), SALARY FLOAT ); --- INSERT INTO INSTRUCTOR (InstructID,InstructNAME,DEP,SALARY) VALUES (1006,'GODWIN','EL',90000), (2991,'NAVEEN','EL',50000), (1892,'SARA','CS',70000), (3001,'PUNITH','CS',55000), (1242,'UMAIR','FINANCE',80000), (1500,'USHA','FINANCE',45000); --- CREATE TABLE STUDENT ( StudentID INT PRIMARY KEY, StudentName VARCHAR(25), Department VARCHAR(25), InstructID INT, FOREIGN KEY (InstructID) REFERENCES INSTRUCTOR (InstructID) ); --- INSERT INTO STUDENT (StudentID, StudentName, DEP, InstructID) VALUES (01, 'ADITYA', 'CS', 1892), (02, 'NOAH', 'Finance', 1242), (03, 'SOPHIE', 'EL', 2991); --- (i) SELECT InstructName FROM INSTRUCTOR WHERE Salary > ANY (SELECT Salary FROM INSTRUCTOR WHERE DEP = 'Finance'); --- (ii) SELECT * FROM INSTRUCTOR WHERE Salary < ALL (SELECT Salary FROM INSTRUCTOR WHERE DEP = 'CS') AND DEP != 'CS'; --- (iii) SELECT StudentName, DEP FROM STUDENT WHERE DEP IN (SELECT DEP FROM STUDENT WHERE StudentName LIKE '%S%');

CREATE TABLE INSTRUCTOR ( InstructID INT PRIMARY KEY, InstructNAME VARCHAR(25), DEP VARCHAR(25), SALARY FLOAT ); --- INSERT INTO INSTRUCTOR (InstructID,InstructNAME,DEP,SALARY) VALUES (1006,'GODWIN','EL',90000), (2991,'NAVEEN','EL',50000), (1892,'SARA','CS',70000), (3001,'PUNITH','CS',55000), (1242,'UMAIR','FINANCE',80000), (1500,'USHA','FINANCE',45000); --- CREATE TABLE STUDENT ( StudentID INT PRIMARY KEY, StudentName VARCHAR(25), Department VARCHAR(25), InstructID INT, FOREIGN KEY (InstructID) REFERENCES INSTRUCTOR (InstructID) ); --- INSERT INTO STUDENT (StudentID, StudentName, DEP, InstructID) VALUES (01, 'ADITYA', 'CS', 1892), (02, 'NOAH', 'Finance', 1242), (03, 'SOPHIE', 'EL', 2991); --- (i) SELECT InstructName FROM INSTRUCTOR WHERE Salary > ANY (SELECT Salary FROM INSTRUCTOR WHERE DEP = 'Finance'); --- (ii) SELECT * FROM INSTRUCTOR WHERE Salary < ALL (SELECT Salary FROM INSTRUCTOR WHERE DEP = 'CS') AND DEP != 'CS'; --- (iii) SELECT StudentName, DEP FROM STUDENT WHERE DEP IN (SELECT DEP FROM STUDENT WHERE StudentName LIKE '%S%');

-- Step 1: Create the INSTRUCTOR table CREATE TABLE INSTRUCTOR ( InstructID INT PRIMARY KEY, InstructName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2) ); -- Step 2: Insert sample data into the INSTRUCTOR table INSERT INTO INSTRUCTOR (InstructID, InstructName, Department, Salary) VALUES (1, 'Alice', 'Physics', 70000), (2, 'Bob', 'Mathematics', 80000), (3, 'Charlie', 'Physics', 75000), (4, 'Dave', 'Chemistry', 72000), (5, 'Sophia', 'Mathematics', 85000), (6, 'Steve', 'Biology', 60000), (7, 'Emily', 'Physics', 68000); -- Query 1: Find instructors whose salary is more than the salary of any employee from the "Physics" department SELECT * FROM INSTRUCTOR WHERE Salary > ANY ( SELECT Salary FROM INSTRUCTOR WHERE Department = 'Physics' ); -- Query 2: Find the instructor name and department name of all instructors working in a department -- with any instructor whose name contains the letter "S" SELECT DISTINCT I1.InstructName, I1.Department FROM INSTRUCTOR I1 WHERE I1.Department IN ( SELECT I2.Department FROM INSTRUCTOR I2 WHERE I2.InstructName LIKE '%S%' ); -- Query 3: Find the name and department name of instructors whose salary is more than all their colleagues' -- salaries in the same department SELECT InstructName, Department FROM INSTRUCTOR I1 WHERE Salary > ALL ( SELECT Salary FROM INSTRUCTOR I2 WHERE I2.Department = I1.Department AND I2.InstructID != I1.InstructID );

-- Step 1: Create the STATION table CREATE TABLE STATION ( ID INT PRIMARY KEY, CITY VARCHAR(25), STATE VARCHAR(20), LAT DECIMAL(9,6), LONG DECIMAL(9,6) ); -- Insert sample data into the STATION table INSERT INTO STATION (ID, CITY, STATE, LAT, LONG) VALUES (1, 'New York', 'NY', 40.7128, -74.0060), (2, 'Los Angeles', 'CA', 34.0522, -118.2437), (3, 'Chicago', 'IL', 41.8781, -87.6298), (4, 'Houston', 'TX', 29.7604, -95.3698), (5, 'Phoenix', 'AZ', 33.4484, -112.0740); -- Step 2: Create the STATS table CREATE TABLE STATS ( ID INT, MONTH VARCHAR(15), TEMPERATURE DECIMAL(5,2), RAINFALL DECIMAL(5,2), PRIMARY KEY (ID, MONTH), FOREIGN KEY (ID) REFERENCES STATION(ID) ); -- Insert sample data into the STATS table INSERT INTO STATS (ID, MONTH, TEMPERATURE, RAINFALL) VALUES (1, 'January', 30.5, 2.5), (2, 'February', 55.2, 3.0), (3, 'March', 45.1, 1.2), (4, 'April', 72.0, 5.3), (5, 'May', 85.0, 0.8); -- Step 3(i): Delete data from STATION where longitude > 90 DELETE FROM STATION WHERE LONG > 90; -- Step 3(ii): Delete July data from STATS table where longitude > 90 DELETE FROM STATS WHERE ID IN ( SELECT ID FROM STATION WHERE LONG > 90 ) AND MONTH = 'July'; -- Step 4: Increase the size of the CITY column ALTER TABLE STATION MODIFY CITY VARCHAR(50); -- Step 5: Rename LONG column to LONGITUDE ALTER TABLE STATION RENAME COLUMN LONG TO LONGITUDE; -- Verification: Display the STATION table SELECT * FROM STATION; -- Verification: Display the STATS table SELECT * FROM STATS;

*7th program* Create Database Temp; Use Temp; ---- CREATE TABLE STATS ( ID INT REFERENCES Station(ID), MONTH INT CHECK (MONTH BETWEEN 1 AND 12), TEMP_Fahr FLOAT CHECK (TEMP_Fahr BETWEEN -80 AND 150), RAIN_Inch FLOAT CHECK (RAIN_Inch BETWEEN 0 AND 100), PRIMARY KEY (ID, MONTH) ); ---- INSERT INTO STATS (ID, MONTH, TEMP_Fahr, RAIN_Inch) VALUES (1, 1, 30.00, 1.50), (2, 1, 25.00, 0.00), (3, 1, 32.00, 0.50), (4, 2, 40.00, 2.00), (5, 3, 50.00, 3.00); ---- CREATE VIEW STATS_METRIC AS SELECT ID, MONTH, ROUND((TEMP_Fahr - 32) * 5/9, 2) AS TEMP_Cels, ROUND(RAIN_Inch * 2.54, 2) AS RAIN_CM FROM STATS; ---- SELECT ID, MONTH, TEMP_Fahr, RAIN_Inch FROM STATS WHERE MONTH = 1 AND TEMP_Fahr < 32 ORDER BY RAIN_Inch;