Overview
This exercise will require participants to query on the tables in Oracle SQL PLUS using the SQL language.
Instructions
Querying records in tables should be done in Oracle SQL PLUS.
1. In order to proceed with querying records in the tables, login using your user name created in Exercise 01.
Hints
1. Ensure the following while querying the table:
§ Execute the query in SQL PLUS
§ After each successful command save the command in the file
§ To save the file - C:\RDBMS\Day2\Queries.sql append
Querying Records
Plain SQL queries
1) Display the structure of the course table
Solution:
DESC COURSE_INFO
2) Display the zipcode, city and state. Observe the column heading of state column. If not appearing correctly, give a proper heading. Save the SQL statement in the sql file C:\RDBMS\Day2\zipquery.sql.
Solution:
SELECT ZIP_CODE, CITY, STATE FROM ZIPCODE_INFO
COL STATE FORMAT A5
SAVE C:\RDBMS\Day2\zipquery.sql
3) Run the zipquery.sql
Solution:
@ C:\RDBMS\Day2\zipquery.sql
4) Display the unique states.
Solution:
5) Display the student_id, name. Concatenate the first_name and last name.
Solution:
SELECT STUDENT_ID, STUDENT_FIRST_NAME||' '||STUDENT_LAST_NAME FROM STUDENT_INFO
6) Display the Zipcode, city and state as a single column. Separate the data with a comma. E.g 400050, Mumbai, MH. Give the column heading as Address.
Solution:
SELECT ZIP_CODE||','||CITY||','||STATE AS ADDRESS FROM ZIPCODE_INFO
7) Display the description of the course
Solution:
SELECT COURSE_NAME FROM COURSE_INFO;
8) In the above query also display the cost
Solution:
SELECT COURSE_NAME, COST FROM COURSE_INFO;
9) Display all the columns of the course_info table
Solution:
SELECT * FROM COURSE_INFO
10) Display the instructor's last name and the zip in which the instructor resides.
Solution:
SELECT INSTRUCTOR_FIRST_NAME, ZIP_CODE FROM INSTRUCTOR_INFO
11) There are many students who are living in the area, display unique zip's only.
Solution:
SELECT DISTINCT ZIP_CODE FROM STUDENT_INFO
12) Write a select statement to list the first and last names of all students.
Solution:
SELECT STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME FROM STUDENT_INFO
13) Write a select statement to list all cities, their states and zip codes.
Solution:
SELECT CITY, STATE, ZIP_CODE FROM STUDENT_INFO
SELECT using WHERE
1) Display the student_id, section_id and numeric_grade of those students who have the grade code type as ‘FI’
Solution:
SELECT STUDENT_ID, SECTION_ID, NUMERIC_GRADE FROM GRADE_INFO WHERE GRADE_TYPE_CODE='FI'
2) Display the Zipcode and cities for the state ‘MI’
Solution:
SELECT ZIP_CODE, CITY FROM ZIPCODE_INFO WHERE STATE='MI'
3) Display the student and section_id of students who have enrolled in the month of Jan 1999. Sort the data in the ascending order of student names. Note: student and section_id should be concatenated.
Solution:
4) Display the section and the instructor id of the course 10 and 20. Sort the data in the ascending order of instructor id.
Solution:
SELECT SECTION_ID, INSTRUCTOR_ID FROM SECTION_INFO WHERE COURSE_NO IN (10, 20) ORDER BY INSTRUCTOR_ID
5) Display the student_id, section_id and numeric_grade. Sort the section_id in ascending order followed by numeric_grade in descending order.
Solution:
SELECT STUDENT_ID, SECTION_ID, NUMERIC_GRADE FROM GRADE_INFO ORDER BY SECTION_ID, NUMERIC_GRADE DESC
6) Display the course_no, description and cost of the courses. The courses should have the word ‘Intro’
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE DESCRIPTION LIKE 'Intro%'
7) Display the course details whose third last letter is ‘a’.
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE DESCRIPTION LIKE '__a%'
8) Display the student names whose student_id is in the range of 300 to 350.
Solution:
SELECT STUDENT_FIRST_NAME, STUDENT_LAST_NAME FROM STUDENT_INFO WHERE STUDENT_ID BETWEEN 300 AND 350
9) Display the course details whose cost ranges from 4000 to 7000.
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE COST BETWEEN 4000 AND 7000
10) Write a select statement that displays the instructor's first name and address whose last name is 'Schumer'
Solution:
SELECT INSTRUCTOR_FIRST_NAME, INSTURCTOR_LAST_NAME, STREET_ADDRESS FROM INSTRUCTOR_INFO WHERE INSTRUCTOR_LAST_NAME ='Schumer'
11) Write a select statement that displays the instructor's first name whose last name is not 'Schumer'
Solution:
SELECT INSTRUCTOR_FIRST_NAME, INSTURCTOR_LAST_NAME, STREET_ADDRESS FROM INSTRUCTOR_INFO WHERE INSTRUCTOR_LAST_NAME !='Schumer'
12) Display the course name and cost of those courses whose cost is more than 4000
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE COST>4000
13) Display the course name and cost of those courses whose cost is in the range of 3000 and 7000.
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE COST BETWEEN 3000 AND 7000
14) Display the course name and cost of those courses whose cost is 4000 and 4500
Solution:
SELECT COURSE_NO, DESCRIPTION, COST FROM COURSE_INFO WHERE COST BETWEEN 4000 AND 4500
15) Write a select statement that displays the student's first name and address whose last name starts with 'S'
Solution:
SELECT STUDENT_FIRST_NAME, STUDENT_LAST_NAME FROM STUDENT_INFO WHERE LAST_NAME LIKE 'S%'
16) Write a select statement that displays the student's first name and address whose last name contains 'o' as the second letter.
Solution:
SELECT STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STREET_ADDRESS FROM STUDENT_INFO WHERE LAST_NAME LIKE '_o%'
17) Write a select statement that displays the instructor's first name and address whose last name does not start with 'S'
Solution:
SELECT INSTRUCTOR_FIRST_NAME, INSTRUCTOR_LAST_NAME, STREET_ADDRESS FROM INSTRUCTOR_INFO WHERE LAST_NAME NOT LIKE 'S%'
18) Display the course name and cost of those courses whose cost is in range of 4000 and 4500 and the course name should start with 'I'
Solution:
SELECT DESCRIPTION, COST FROM COURSE_INFO WHERE COST BETWEEN 4000 AND 4500 AND DESCRIPTION LIKE 'I%'
19) Display the course name and cost and prerequisite of those courses whose cost is 2000 and prerequisite is 20 and all courses whose prerequisite is 25
Solution:
SELECT DESCRIPTION, COST, COURSE_PREREQUISITE FROM COURSE_INFO WHERE (COST=2000 AND PREREQUISITE=20) OR PREREQUISITE=25
20) Display the course name and cost and prerequisite of those courses whose cost is 2000 and all records whose prerequisite is 20 and 25.
Solution:
SELECT DESCRIPTION, COST, COURSE_PREREQUISITE FROM COURSE_INFO WHERE COST=2000 AND (PREREQUISITE=20 OR PREREQUISITE=25)
21) Display the records of the course_info table who has no prerequisite
Solution:
SELECT DESCRIPTION, COST, COURSE_PREREQUISITE FROM COURSE_INFO WHERE COURSE_PREREQUISITE IS NULL
22) Write a select statement to list the last names of students living either in zip code 10048, 11102 and 11209.
Solution:
SELECT STUDENT_LAST_NAME FROM STUDENT_INFO WHERE ZIP_CODE IN ('10048','11102','11209')
23) Write a select statement to list the first and last names of instructors with the letter 'i' in any case in the last name and living in zip code 10025.
Solution:
SELECT INSTRUCTOR_FIRST_NAME, INSTRUCTOR_LAST_NAME FROM INSTRUCTOR_INFO WHERE LOWER (LAST_NAME) LIKE '%i%' AND ZIP_CODE='10025'
24) Write a select statement to list descriptions of courses who have prerequisites and cost is less than 1100
Solution:
SELECT DESCRIPTION, COST, PREREQUISITE FROM COURSE_INFO WHERE COURSE_PREREQUISITE IS NOT NULL AND COST <1100
25) Write a select statement to list the cost of courses that do not a prerequisite. In the result the cost should not be repeated.
Solution:
SELECT DISTINCT COST FROM COURSE_INFO WHERE PREREQUISITE IS NULL
26) Display the course_no and the coursename who do not a prerequisite. Sort the data on the basis of coursename
Solution:
SELECT COURSE_NO, DESCRIPTION FROM COURSE_INFO WHERE PREREQUISITE IS NULL ORDER BY DESCRIPTION
27) For the above query sort in the descending order of coursename
Solution:
SELECT COURSE_NO, DESCRIPTION FROM COURSE_INFO WHERE PREREQUISITE IS NULL ORDER BY DESCRIPTION DESC
28) Write a select statement to list each city in state New York or Connecticut sorted in ascending order by zipcode.
Solution:
SELECT CITY FROM ZIPCODE_INFO WHERE STATE IN ('NY','CT') ORDER BY ZIP_CODE
29) Display the zip, first and last name of students with the last name Graham. Order the result by zip in descending order and the first name in ascending order.
Solution:
SELECT STUDENT_FIRST_NAME, STUDENT_LAST_NAME FROM STUDENT_INFO WHERE STUDENT_LAST_NAME='Graham' ORDER BY ZIP_CODE DESC, ORDER BY FIRST_NAME ASC
30) Display the city and state as 1 column.
Solution:
SELECT CITY||' , '|| STATE FROM ZIPCODE
31) Display the first name of the student. Only the first letter of the first name should be in uppercase.
Solution:
SELECT INITCAP (STUDENT_FIRST_NAME) FROM STUDENT_INFO
32) Write a select statement that displays the instructor's first name followed by comma and a space which is followed by last_name. The result should be displayed in 1 result set.
Solution:
SELECT FIRST_NAME||' , '|| LAST_NAME AS NAME FROM INSTRUCTOR_INFO
33) Display the select statement that displays the cost, add 10 to cost, subtract 20 from cost, multiply cost by 30 and divide cost by 5
Solution:
SELECT COST, COST + 10 AS TOTAL, COST - 20 AS SUB, COST * 30 AS MUL, COST/30 AS DIV FROM COURSE
34) Write a select statement that displays unique numeric grade, half these values and do not display this value in decimals. This calculation should be displayed as a separate column.
Solution:
SELECT DISTINCT NUMERIC_GRADE, ROUND (NUMERIC_GRADE/2) FROM GRADE_INFO
SELECT with TO_CHAR/DECODE/IS NULL
1) Display the cost in the following format for all the courses whose course_no is less than 25.
EX. 4,000
3,500
Solution:
SELECT TO_CHAR (COST,'9,999') AS COST FROM COURSE_INFO WHERE COURSE_NO=25
2) Write a query to format the cost column. The cost displayed should have a leading $ sign followed by a comma to separate the thousands and should display 2 decimals. E.g $2,000.00
Solution:
SELECT TO_CHAR (COST,'$9,999.99') FROM COURSE_INFO
3) For those courses having no pre-requisites display data as 'Not Applicable'
Solution:
SELECT NVL (TO_CHAR (COURSE_PREREQUISITE),'Not Applicable') FROM COURSE_INFO
4) Display the state as 'New York' for NY, 'New Jersey' for NY and 'Others' for any other state.
Solution:
SELECT DECODE(STATE, 'NY', 'New York', 'NY', 'New Jersey', 'OTHERS') FROM ZIPCODE_INFO
0 comments:
Post a Comment