/*Using the STUDENT table in the MISLab1 database in Omnymbus, perform the following tasks: Note the first SELECT is there to label the output, DUAL is a "dummy" table. The second SELECT is the solution.
1. Write a SQL statement to display Student's First and Last Name.*/
SELECT ' Result 1 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT;
/*2. Write a SQL statement to display the Major of the STUDENT with no duplications. Do not display student names.*/
SELECT ' Result 2 ' AS 'Result Table' from DUAL;
SELECT Distinct STUDENT.Major
FROM STUDENT;
/*3. Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622. */
SELECT ' Result 3 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622";
/*4. Write a SQL statement to display the First and Last Name of students who live in the Zip code 97912 and have the major of CS.*/
SELECT ' Result 4 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622" AND STUDENT.MAJOR="CS";
/*5. Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622 or 37311. Do not use IN.*/
SELECT ' Result 5 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622" OR STUDENT.ZIP ="37311";
/*6. Write a SQL statement to display the First and Last Name of students who have the major of Business or Math. Use IN.*/
SELECT ' Result 6 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.MAJOR IN ("Business","Math");
/*7. Write a SQL statement to display the First and Last Name of students who have the Class greater than 1 and less than 10. Use the SQL command BETWEEN. */
SELECT ' Result 7 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.CLASS BETWEEN 1 AND 10;
/*8. Write a SQL statement to display the First and Last Name of students who have a Last name that starts with an S.*/
SELECT ' Result 8 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.Last_Name LIKE "S%";
/*9. Write a SQL statement to display the First and Last Name of students having an a in the second position of their first names.*/
SELECT ' Result 9 ' AS 'Result Table' from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.First_Name LIKE '_a';
/*10. Write a SQL expression to display each Status and the number of occurrences of each status using the Count(*) function; display the result of the Count(*) function as CountStatus. Group by Status and display the results in escending order of CountStatus.*/
SELECT ' Result 10 ' AS 'Result Table' from DUAL;
SELECT STUDENT.STATUS, Count(*) AS CountStatus
FROM STUDENT
GROUP BY STUDENT.STATUS
ORDER BY CountStatus ASC;
Attachment:- iLab 1.txt
Attachment:- iLab1QueryScript.sql