Week 1 Submission Essay

1132 WordsMar 14, 20125 Pages
Homework (100 points) Part 1 Create the tables from demo.sql script Download and Copy the demo.sql file from the Doc Share to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql. This will create some tables and insert data into them. View the script in notepad to determine the table names that were created. Use the describe command to view the structure of the tables. Please use the template below to provide your solutions. Write SQL statements to solve the following requests. Question (4 pts per question) | SQL statement or answer | 1. List all employee information in department 30. | SELECT * FROM emp WHERE deptno = 30; | 2. List employees name, job, and salary that is a manager and…show more content…
Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly. | | 13. Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order. | SELECT COURSE_NO, DESCRIPTION, COSTFROM COURSE WHERE COST = 1195, DESCRIPTION != 'INTRO%', PREQUISITES DESC; | 14. Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names. | SELECT * FROM student WHERE (WHERE last_name LIKE '[A-T]') AND employer = 'Competrol Real Estate'; | 15. Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns. | SELECT student_id, last_name FROM student WHERE SUBSTR( last_name, 1,1 )= SUBSTR (LOWER (last_name), 1,1) | 16. Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format. | Select phone, last_name from instructor where translate (phone, '0123456789','##########') <> '(###)###-####'; | 17. Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype. | | 18.
Open Document