DBMS & SQL Assignment 1

DBMS AND SQL ASSIGNMENT-1

Question 1: Provide the create table syntax to Create a Table Employee whose details areas below. Employee(EmployeeID, LastName, FirstName, Address, DateHired) Answer : CREATE TABLE Employee(EmployeeID Employee(EmployeeID int,LastName char(50),FirstName char(50),FirstName char(50),Address char(50),Address char(50),DateHired char(50),DateHired date)

Question 2: Provide the INSERT query to be used in Employee Table to fill the Details. Answer : INSERT INTO Employee( EmployeeID,LastName,FirstName,A EmployeeID,LastName,FirstName,Address,DateHired)Va ddress,DateHired)Values lues (value1,value2,value3,value4,value5)

Question 3: When we give SELECT * FROM EMPLOYEE .How does it Respond? Answer : We use SELECT* from EMPLOYEE when we want to select s elect all the columns from the³employee´ tableSample O/P:SELECT * FROM EmployeeEmployeeID LastName FirstName Address DateHiredValue 1 Value2 Value3 Value4 Value5

Question 4: Create a Table CLIENT whose details are as below.Client(ClientID, LastName, FirstName, Balance, EmployeeID) Answer : CREATE TABLE Client(ClientID int,LastName char(50),FirstName char(50),FirstName char(50),Address char(50),Address char(50),EmployeeId char(50),EmployeeId date)

Question 5: Provide the INSERT query to be used in CLIENT Table to fill the Details. Answer : INSERT INTO Client(ClientID,LastNam Client(ClientID,LastName,FirstName,Addr e,FirstName,Address, ess, EmployeeId)Values EmployeeId)Values (value1,value2,value3,value4,value (value1,value2,value3,value4,value5) 5)

Question 6: When we give SELECT * FROM CLIENT .How does it Respond? Answer : We use SELECT* from Client when we want to select all the colums from the³Client´ tableSample O/P:SELECT * FROM ClientClientID LastName FirstName Address EmployeeIDValue 1 Value2 Value3 Value4 Value5

Question 7: Choose the correct answer. The SQL command to create a table is:a. Make Tableb. Alter Tablec. Define Tabled. Create Table Answer : D. create table

Question 8: Choose the correct answer. The DROP TABLE statement:a. deletes the table structure onlyb. deletes the table structure along with the table datac. works whether or not referential integrity constraints would be violatedd. is not an SQL statement Answer : b.deletes the table structure along with the table data

Question 9: What are the different data types available in SQL server? Answer : Numeric: Stores numeric values.Monetary: It stores numeric values with decimal places. It is used specially for currency values.Data and Time: It stores date and time information.Character: It supports character based values of varying lengths. Binary: It stores data in strict binary (0 or 1) Representation.Special purpose: SQL Server contains Complex data types to handle the XML Documents,Globally unique identifiers etc.

Question 10: Which is the subset of SQL commands used to manipulate Oracle Databasestructures, including tables? Answer : Data Definition Language (DDL)

Question 11: What operator performs pattern matching? Answer : LIKE operator

Question 12: What operator tests column for the absence of data? Answer : IS NULL operator

Question 13: Which command executes the contents of a specified file? Answer : START or @

Question 14: What is the parameter substitution symbol used with INSERT INTOcommand? Answer : &

Question 15: Which command displays the SQL command in the SQL buffer, and thenexecutes it? Answer : RUN

Question 16: What are the wildcards used for pattern matching? Answer : _ for single character substitution and % for multi-character substitution

Question 17: State whether true or false. EXISTS, SOME, ANY are operators in SQL. Answer : True

Question 18: State whether true or false. !=, <>, ^= all denote the same operation. Answer : True

Question 19: What are the privileges that can be granted on a table by a user to others? Answer : Insert, update, delete, select, references, index, execute, alter, all

Question 20: What command is used to get back the privileges offered by the GRANTcommand? Answer : REVOKE

Question 21: Which system tables contain information on privileges granted and privilegesobtained? Answer : USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

Question 22: Which system table contains information on constraints on all the tablescreated? Answer : USER_CONSTRAINTS

Question 23: What is the difference between TRUNCATE and DELETE commands? Answer : TRUNCATE is a DDL command whereas DELETE is a DML command. HenceDELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

Question 24: What command is used to create a table by copying the structure of anothertable? Answer : CREATE TABLE .. AS SELECT commandExplanation :To copy only the structure, the WHERE clause of the SELECT command should contain aFALSE statement as in the following.CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;If the WHERE condition is true, then all the rows or rows satisfying the condition will be copiedto the new table.