DBMS_SQL_TCS_PREILP_ASSIGNMENT

Question 1: Provide the create table syntax to Create a Table Employee whose details are as below.
Employee(EmployeeID, LastName, FirstName, Address, DateHired)

Ans 1: CREATE TABLE `aspire`.`Employee` ( `EmployeeID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `LastName` VARCHAR( 30 ) NOT NULL , `FirstName` VARCHAR( 30 ) NOT NULL , `Address` TEXT NOT NULL , `DateHired` DATE NOT NULL );

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

Ans 2: INSERT INTO `aspire`.`Employee` (`EmployeeID`, `LastName`, `FirstName`, `Address`, `DateHired`) VALUES (NULL, 'Your_Last_Name', 'Your_First_Name', 'Your_Address', '2012-03-22');

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

Ans 3: When we give the questioned query it returns all the rows in the EMPLOYEE table with each resulting row containing values from all the columns of that table. e.g. Suppose currently Employee table has details of 4 employees. So executing the query "SELECT * FROM EMPLOYEE" will return employeeid, lastname, fisrtname, address and datehired associated with all those 4 employees.

Question 4: Create a Table CLIENT whose details are as below.
Client(ClientID, LastName, FirstName, Balance, EmployeeID)

Ans 4: CREATE TABLE `aspire`.`Client` (`ClientID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`LastName` VARCHAR( 30 ) NOT NULL , `FirstName` VARCHAR( 30 ) NOT NULL ,`Balance` DOUBLE NOT NULL , `EmployeeID` foreign key references Employee(EmployeeID) );

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

Ans 5: INSERT INTO `aspire`.`client` (`ClientID`, `LastName`, `FirstName`, `Balance`, `EmployeeID`) VALUES (NULL, 'Your_Last_Name', 'Your_First_Name', 500, '1');

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

Ans 6: When we give the questioned query it returns all the rows in the CLIENT table with each resulting row containing values from all the columns of that table. e.g. Suppose currently CLIENT table has details of 4 clients. So executing the query "SELECT * FROM CLIENT " will return ClientID, LastName, FirstName, Balanceand and EmployeeID associated with all those 4 clients.

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

Ans 7: The correct answer is option d i.e. 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

Ans 8:  The correct answer is option b i.e. deletes the table structure along with the table data.

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

Ans 9: Different types of SQL Server Data Type:

    * Exact Numeric - Bigint, Int, Smallint, Tinyint, Money, Decimal, Numeric and Bit.
    * Approximate Numerics - Float and Real.
    * Date and Time - Date, Datetime, Smalldatetime, Datetimeoffset, Time and Datetime2.
    * Character Strings - Char, Varchar and Text.
    * Unicode Character Strings - nchar, nvarchar and ntext.
    * Binary Strings - Binary, Varbinary and Image.
    * Other - Timestamp, Uniqueidentifier, Xml, Cursor, Sql_variant and Table.


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

Ans 10: DDL i.e. Data Definition Language.

Question 11: What operator performs pattern matching?

Ans 11: Like Operator.

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

Ans 12: IS NULL Operator.

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

Ans 13: START or @ command.

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

Ans 14: '&' commonly known as ampersand operator.

Question 15: Which command displays the SQL command in the SQL buffer, and then executes it?

Ans 15: Run.

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

Ans 16:  _ and %. _ for single character substitution, % for multiple character substitution,  [CHARLIST] for any single charecter in CHARLIST and ^[CHARLIST] for any single charecter NOT in CHARLIST.

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

Ans 17: True.

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

Ans 18: True.

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

Ans 19: INSERT, UPDATE, DELETE, SELECT, REFERENCES, INDEX, EXECUTE, ALTER and ALL.

Question 20: What command is used to get back the privileges offered by the GRANT command?

Ans 20: REVOKE.

Question 21: Which system tables contain information on privileges granted and privileges obtained?

Ans 21: For privilages granted - USER_TAB_PRIVS_MADE and for privilages received -   USER_TAB_PRIVS_RECD .

Question 22: Which system table contains information on constraints on all the tables created?

Ans 22: USER_CONSTRAINTS.

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

Ans 23:

    * DELETE is a DML command and TRUNCATE is a DDL command.
    * Where clause can be used with delete only.
    * The DELETE operation can be rolled back, TRUNCATE operation cant be rolled back again that is it is a permanent delete.



Question 24: What command is used to create a table by copying the structure of another table?

Ans 24: The syntax's for creating a table by copying the structure of another table are:

   1. For copying the entire sturcture - CREATE TABLE COPY_TABLENAME AS SELECT * from ORIGINAL_TABLENAME.
   2. For copying partial sturcture - CREATE TABLE COPY_TABLENAME AS SELECT COL1, COL2,.... from ORIGINAL_TABLENAME.

e.g CREATE TABLE aspire.EmployeeCopy AS SELECT * from aspire.Employee