DBMS AND SQL ASSIGNMENT-1
Question 1: Provide the create table syntax to Create a Table Employee whose details are as below. Employee(EmployeeID, LastName, FirstName, Address, DateHired) Answer : CREATE TABLE Employee (EmployeeID int, LastName char(50), FirstName char(50), Address 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,Fi EmployeeID,LastName,FirstName,Address,DateHired) rstName,Address,DateHired) Values (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 all the columns from the ³employee´ table
Sample O/P:SELECT * FROM Employee EmployeeID LastName FirstName Value 1 Value2 Value3
Address Address Value4
DateHired 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), Address 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,LastName,FirstName,Address, E mployeeId) Values (value1,value2,value3,value4,value5) 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´ table Sample O/P:SELECT * FROM Client
ClientID Value 1
LastName Value2
FirstName Value3
Address Value4
EmployeeID Value5
Question 7: Choose the correct answer. The SQL command to create a table is: a. Make Table b. Alter Table c. Define Table d. Create Table
Answer : D. create table
Question 8: Choose the correct answer. The DROP TABLE statement: a. deletes the table structure only b. deletes the table structure along with the table data c. works whether or not referential integrity constraints would be violated d. 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 cu rrency values. Data and Time: It stores date and time information. Character: It supports character based values of var ying lengths.
Binary: It stores data in strict binary (0 or 1) Representation. Special purpose: SQL Server contains Complex data t ypes to handle the XML Documents, Globally unique identifiers etc. Question 10: Which is the subset of SQL commands used to manipulate Oracle Database structures, 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 INTO command? Answer : & Question 15: Which command displays the SQL command in the SQL buffer, and then executes 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 o perators 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 GRANT command?
Answer : REVOKE
Question 21: Which system tables contain information on privileges granted and privileges obtained?
Answer : USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD Question 22: Which system table contains information on constraints on all the tables created?
Answer : USER_CONSTRAINTS
Question 23: What is the difference between TRUNCATE and DELETE commands?
Answer : TRUNCATE is a DDL command whereas DELET E is a DML command. Hence DELETE 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 another table?
Answer : CREATE TABLE .. AS SELECT command Explanation : To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following. CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2; If the WHERE condition is true, then a ll the rows or rows satisfying the condition will be cop ied to the new table.
