Monisha Blosom

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%');

*6th Program* Create database weather2; --------------- Use weather2; --------------- CREATE TABLE STATS ( ID INT, MONTH INT CHECK (MONTH BETWEEN 1 AND 12), TEMP_Fahr DECIMAL(5,2) CHECK (TEMP_Fahr BETWEEN -80 AND 150), RAIN_Inch DECIMAL(5,2) CHECK (RAIN_Inch BETWEEN 0 AND 100), PRIMARY KEY (ID, MONTH) ); ------------- INSERT INTO STATS (ID, MONTH, TEMP_Fahr, RAIN_Inch) VALUES (1, 1, 57.4, 0.31), (1, 7, 91.7, 5.15), (2, 1, 45.0, 0.10), (2, 7, 85.0, 2.50), (3, 1, 32.0, 0.00), (3, 7, 75.0, 1.20); -------------- 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; --------------- ALTER TABLE STATS ADD RAINFALL_CENTIMETER DECIMAL(5,2); --------------- UPDATE STATS SET RAINFALL_CENTIMETER = ( SELECT RAIN_CM FROM STATS_METRIC WHERE STATS.ID = STATS_METRIC.ID AND STATS.MONTH = STATS_METRIC.MONTH ); -------------- ALTER TABLE STATS DROP COLUMN RAIN_Inch;

8. Create table called STATS to store normalized temperature and precipitation data 1) Update all rows of table STATS to compensate for faulty rain gauges knownto read 0.01 inches low Update one row, ID 44's July temperature reading, to correct a data entryerror )س( Make the above changes permanent iv) Undo that update

Observation program for tomorrow’s lab. Write a menu driven program to create orderd linked list 1.insert a list node 2.Display linked list 3.Delete a given number 4.Delete the first number 5.return max number 6.Display number of nodes 7.Delete last number