List output from the following queries. (Uses tables created in the Chapter 4 Lab Activity)

Question

1. SELECT INITCAP(First) || ‘ ’ || INITCAP(Last) “Student Name”
FROM student;
2. SELECT COUNT(DISTINCT (MajorId)) FROM student;
3. SELECT Building, SUM(Capacity) TOTCAP
FROM location
GROUP BY Building
ORDER BY TOTCAP DESC;
4. SELECT UPPER(Lname), UPPER(Fname), (SYSDATE – HIREDATE) DAYS
FROM employee
ORDER BY DAYS;
5. SELECT Building, AVG (Capacity)
FROM location
GROUP BY Building
HAVING AVG (Capacity) > 25;

Answer 1

1.
SQL> SELECT INITCAP(First) || ' ' || INITCAP(Last) "Student
Name"
2 FROM student;
Student Name
-------------------------------
Jose Diaz
Mickey Tyler
Rajesh Patel
Deborah Rickles
Brian Lee
Amir Khan
6 rows selected.
2.
SQL> SELECT COUNT(DISTINCT (MajorId)) FROM student;
COUNT(DISTINCT(MAJORID))
------------------------
5
3.
SQL> SELECT Building, SUM(Capacity) TOTCAP
2 FROM location
3 GROUP BY Building
4 ORDER BY TOTCAP DESC;
BUILDIN TOTCAP
------- ----------
Kennedy 155
Nehru 95
Gandhi 10
4.
SQL> SELECT UPPER(Lname), UPPER(Fname), (SYSDATE - HIREDATE) DAYS
2 FROM employee
3 ORDER BY DAYS;
UPPER(LNAME) UPPER(FNAME) DAYS
--------------- --------------- ----------
SHAW JINKU 1633.79684
CHEN SUNNY 1774.79684
MCCALL ALEX 2601.79684
GARNER STANLEY 3037.79684
DEV DEREK 3388.79684
ROBERTS SANDI 4587.79684
HOUSTON LARRY 13550.7968
SMITH JOHN 16140.7968
8 rows selected.
5.
SQL> SELECT Building, AVG (Capacity)
2 FROM location
3 GROUP BY Building
4 HAVING AVG (Capacity) > 25;
BUILDIN AVG(CAPACITY)
------- -------------
Kennedy 38.75
Nehru 47.5