Note: EffectiveDate is the date from which a salary is effective. For example if

Question

Note: EffectiveDate is the date from which a salary is effective. For example if a staff starts working as a sales man from August 15th 2005 with salary 40,000, and gets a salary raise to 420000 on June 1st 2006, the StaffPosition table contains only one row with starting date August 15th 2005 and EndingDate NULL but the StaffSalary table will contain two rows, one with effective date August 15th 2005 and another with June 1st 2006. Thus the present position of the staff is SalesMan and present salary is 42,000. EndingDate NULL in StaffPosition table means current position of the staff.

Questions: 1. Write a SQL statement to alter the table Staff to change the data type of StaffCity to char(20). What is the effect of this operation on storage efficiency? Justify your answer, showing necessary SQL queries. Based on your answer, explain when CHAR and VARCHAR2 should be used. 2. Show use of the UPDATE command using any two tables in the database. 3. Show use of the DELETE command using any two tables in the database. 4. List all the tables you have created using a SELECT query. 5. List the names and types of all the constraints for table Screens using a SELECT query. 6. Add a new column PositionResponsibilities of type VARCHAR2 into table Position. 7. What does the SQLPLUS command ED do? 8. How can you extract the time portion from a column of type DATE? How can you insert only a time (e.g., 10:15AM) into a column of type DATE? Give examples using the PlayingInScreen table in this database. 9. Modify the check constraint of column ScreenSeatingCapacity of table Screen so that its value is >0 and <250. Use the BETWEEN operator. 10. How can you insert a string containing an '&' and 'single quote' into a column of type varchar2? For example, how to correctly insert the string “Bed, Bath & Beyond is next to Macy's.” into a table TEST with column TEST_COL of type VARCHAR2(100). 11. Drop all the tables in the database in such a way that no foreign key constraint errors occur

Table: Staff Attribute Type Staff Id Char (8) Staff Name Varchar (40) Staff StreetAddress Varchar2 (100) Constraint Pk. StaffCity Varchar2 (20) Varchar 2020 Staff State Varchar2 Staff Zipcode Varchar2 (16) Staff Phone Number Staff Email Varchar2 (40 Char Gender "M" or "F" ISSN Varchar2 (9) Not Null Not Null DOB Date

Explanation / Answer

1. Write a SQL statement to alter the table Staff to change the data type of StaffCity to char(20). What is the effect of this operation on storage efficiency? Justify your answer, showing necessary SQL queries. Based on your answer, explain when CHAR and VARCHAR2 should be used.

ALTER TABLE Staff
ALTER COLUMN StaffCity Char(20);

Varchar is used to store variable length datatype whereas Char is used to store fixed length data type. Here we are limiting the StaffCity length to 20. It wont take more than 20 characters and for StaffCity less than 20 characters the left out spaces will be blank.

2. Show use of the UPDATE command using any two tables in the database.

UPDATE Theater
SET State = 'CONNECTICUT'
WHERE City = "HARTFORD';

UPDATE Screen
SET ScreenName = 'Alfred'
WHERE TheaterId = '123AH';

3. Show use of the DELETE command using any two tables in the database

DELETE FROM Director
WHERE DirectorName ='Charlie Shapiro';

DELETE FROM Ticket
WHERE ScreenID = '4A';

4.List all the tables you have created using a SELECT query.

SELECT * FROM (SHOW TABLES) AS 'My_Tables';

5. List the names and types of all the constraints for table Screens using a SELECT query.

SELECT * FROM user_constraints
WHERE table_name = 'Screen';

6. What does the SQLPLUS command ED do.

ED command is used to edit the query in the buffer.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.