TCS ASPIRE : DATABASE ASSIGNMENT

Question 1: Provide the create table syntax to Create a Table Employee whose details are as below.

Employee(EmployeeID, LastName, FirstName, Address, DateHired)

Create Table Employee (EMP_NO NUMBER (4) NOT NULL, LAST_NAME VARCHAR (25), FIRST_NAME VARCHAR (25), ADDRESS VARCHAR (50), DATE_HIRED DATE);

Question 2: Provide the INSERT query to be used in Employee Table to fill the Details.

INSERT INTO Employee (EMP_NO, LAST_NAME, FIRST_NAME, ADDRESS, DATE_HIRED) VALUES (311,’sayed’, ‘makdoom’,’ 311, radha aprts, kurla depot, mumbai’, SYSDATE);

Question 3: When we give SELECT * FROM EMPLOYEE .How does it Respond?

The command retrieves all the records from all the columns of the Employee table, i.e., it retrieves the entire data inserted into the table Employee.  

Question 4: Create a Table CLIENT whose details are as below.

Client(ClientID, LastName, FirstName, Balance, EmployeeID)

Create Table Client (CLIENT_NO NUMBER (3) NOT NULL, LAST_NAME VARCHAR (40), FIRST_NAME VARCHAR (40), BALANCE NUMBER (10), EMPLOYEE_ID NUMBER (5));

Question 5: Provide the INSERT query to be used in CLIENT Table to fill the Details.

INSERT INTO Client (CLIENT_NO, LAST_NAME, FIRST_NAME, BALANCE, EMPLOYEE_ID) VALUES (311,’sayed’, ‘makdoom’, 502, 31150);

Question 6: When we give SELECT * FROM CLIENT .How does it Respond?

The command retrieves all the records from all the columns of the Client table, i.e., it retrieves the entire data inserted into the table Client.

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

 sol :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

sol: Deletes the table structure along with the table data

Question 9: What are the different data types available in SQL server?

The different data types available in SQL server are: 

 Exact Numeric

 Approximate Numeric

Date and Time

Character Strings

 Unicode Character Strings

Binary Character Strings

Question 10: Which is the subset of SQL commands used to manipulate Oracle Database structures, includingtables?

The Data Definition Language (DDL) commands are used to manipulate the Oracle Database structures, including tables. These include:

Create

 Alter

 Drop

  Rename

 Truncate

 Comment

Question 11: What operator performs pattern matching?

“LIKE" is the operator used in pattern matching.
 

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

The IS NULL operator tests column for the absence of data.

 Question 13: Which command executes the contents of a specified file?

  The command “START <filename> or @<filename>” executes the contents of a specified file
 

Question 14: What is the parameter substitution symbol used with INSERT INTO command?

The & symbol is the parameter substitution symbol used with INSERT INTO command.

 Question 15: Which command displays the SQL command in the SQL buffer, and then executes it?
 The RUN command is used to display the SQL command in the SQL buffer and then execute it.

Question 16: What are the wildcards used for pattern matching?

The wildcards used for pattern matching are: 

    _ : for single character substitution  

   % : for multi-character substitution

 

Question 17: State whether true or false.

EXISTS, SOME, ANY are operators in SQL.

True. EXISTS, SOME, ANY are operators in SQL.

 Question 18: State whether true or false.

!=, <>, ^= all denote the same operation.

True. !=, <>, ^= all denote the same operation. They denote not equal to.

Question 19: What are the privileges that can be granted on a table by a user to others?
 The privileges that can be granted on a table by a user to others are: 

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?

The REVOKE command is used to get back the privileges offered by the GRANT command.
 

Question 21: Which system tables contain information on privileges granted and privileges obtained?
 The tables USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD contain information on privileges granted and privileges obtained.

Question 22: Which system table contains information on constraints on all the tables created?
 The table USER_CONSTRAINTS contains all the constraints that have been set on all the tables for that particular session. When a new session is started, or a new user logs in the constraints mentioned in the USER_CONSTRAINTS may differ.

Question 23: What is the difference between TRUNCATE and DELETE commands?
 

 The DELETE command is used to remove rows from a table.

  A WHERE clause can be used to remove some rows only.

  If no WHERE condition is specified, all rows will be removed.

  After performing a DELETE operation you need to COMMIT the transaction to make the change permanent.

  If not committed, the change can be rolled back using the ROLLBACK statement, so the deleted data can be restored.

  This operation will cause all DELETE triggers on the table to fire.

 The TRUNCATE command removes all rows from a table.

  The operation cannot be rolled back, i.e., the operation is permanent.

  NO triggers will be fired.

  As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Question 24: What command is used to create a table by copying the structure of another table?
 The command CREATE TABLE... AS SELECT is used to create a table by copying the structure of another table.

Top of Form

Bottom of Form