Reference no: EM13835306

/*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