SQL Theory Questions & Answers

> How many kinds of statement SQL have?

SQL have 3 kind of statements as following.
DDL (Data Definition Language)
    CREATE – To create objects in the database
    ALTER  – To alter the structure of the database
    DROP   – To delete objects from the database
    TRUNCATE – To remove all records from a table. The  space allocated for the records is also removed

DML (Data Manipulation Language):
    SELECT – To retrieve data from the database
    INSERT – To insert data into a table
    UPDATE – To update existing data within a table
    DELETE – To delete all records from a table. Delete statement does not release space.

DCL (Data Control Language ):
    GRANT     – To give user access privileges to database objects
    REVOKE  – To withdraw access privileges given with the GRANT command
    COMMIT  – To save the work done
    ROLLBACK – To restore database to original since the last COMMIT

> How many types of constraints are in SQL ?

Constraints are the rule that restricts the values in a database.

1.   PRIMARY KEY
2.   NOT NULL
3.   UNIQUE
4.   CHECK
5.   FOREIGN KEY

> What is the difference between primary key and unique key?
 
Primary Key:
i  ) Can be only one in a table.
ii ) It never allows null values.
iii) Primary Key is unique key identifier and can not be null and must be unique.

Unique Key:
i  ) Can be more than one unique key in one table.
ii ) Unique key can have one or multiple null but value can not duplicate.
iii) It can’t be candidate key
iv ) Unique key can be null and may not be unique.
 
> What is the difference between TRUNCATE and DELETE commands?

•   DELETE is a DML command whereas TRUNCATE is a DDL command.Hence DELETE operation can be rolled back but TRUNCATE can not.
•   DELETE does not release the memory occupied by the records of the table .TRUNCATE releases the memory occupied by the records of the table
•   WHERE clause can be used with DELETE and not with TRUNCATE.

> Explain UNION, MINUS, UNION ALL and INTERSECT?

    INTERSECT - Return all distinct rows but conman records exist in both queries.
    Union     - Return all distinct rows from both queries.
    Union All - Returns all rows of both queries.
    MINUS     - Returns all distinct rows selected by the first query but not by the second

Example:
SELECT new_id from tab1 INTERSECT SELECT new_id FROM TAB2;

> How many types of join explain with example.
1) Cartesian join
2) Inner join
3) Outer join
    -Left-outer join
    -Right-outer join
4) Self join

1) Cartesian join:
    A Cartesian join occurs when data is selected from two or more tables and there is no common relation specified in the WHERE clause.
    If the first table has 3 rows and the second table has 4 rows, the result will have 12 rows.
    Example:
    SELECT * FROM Table1,Table2;
  
2) Inner join:
    Inner join is the cartesian product between 2 or more tables which satisfies the join condition in the WHERE clause .
    Example:
    SELECT Table1.Emp_ID, Table1.City, Table2.Cust_ID, Table2.City
    FROM Table1, Table2
        WHERE Table1.City = Table2.City;

3) Outer join
    -Left-outer join :
    A left outer join will return all the rows that an inner join returns and also return those rows from left table which does not have match in right side table.

    -Right-outer join
    A right outer join will return all the rows that an inner join returns and also return those rows from right table which does not have match in lest side table.

4) Self join:
    When you wish to join a table with itself based on some criteria then treat the table as two different tables by giving synonyms.
    Example:
    Consider a table Employee_Manager with column Emp_id,Emp_name and Mgr_id
    SELECT     Emp.Employee_ID , Emp.Employee_Name ,
    Emp.Mgr_id , Manager.Employee_Name
     FROM employee_Manager  Emp, employee_Manager  Manager
        WHERE Emp.Mgr_id  =  Manager.Employee_ID;
      
> What is difference between Stored Procedure and Trigger?

• For executing SP(Stored Procedure) we have to call Stored Procedure. But in case of Trigger it is call whenever there is an action takenon table or column(Insert, Update ,Delete).
• Stored procedure can take input parameters, but we can not pass parameters as input to a trigger.
• Stored procedure returns value but trigger cannot returns value.
• Stored procedures can be scheduled through a job to execute on a predefined time, but we can not schedule a trigger.
• We can use Print commands inside a stored procedure for debugging purposes but we can not use print commands inside a trigger.
• Stored procedures are used for performing tasks while Triggers used for auditing work.

> What is difference between procedure and function?

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Functions can be called from Procedure whereas Procedures cannot be called from Function.

> How to add constraints while creating table
  CREATE TABLE Customer1(
    customerid varchar2(4) CONSTRAINT cust1_pkey PRIMARY KEY CHECK (customerid like 'C%'),
    customername varchar2(20) CONSTRAINT custname_nnull NOT NULL,-- DEFAULT 'XXX',
    emailid varchar2(20) CONSTRAINT email_unique UNIQUE,
    accountno NUMBER CONSTRAINT acc_fkey REFERENCES accounts(accno)
);

--MySQL DB
CREATE TABLE `staging`.`DailyCheck` (
   `date` date NOT NULL DEFAULT '0000-00-00',
   `campaignid` varchar(255) NOT NULL DEFAULT '',
   `campaign` varchar(255) NOT NULL,
   `imps` int(11) NOT NULL DEFAULT '0',
   `clicks` int(11) NOT NULL DEFAULT '0',
   `cost` double NOT NULL DEFAULT '0',
   PRIMARY KEY (`campaignid`,`date`),
   KEY `datidx` (`date`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 CREATE TABLE CUSTOMER(
    customerid varchar(4) NOT NULL,
    customername varchar(20) NOT NULL,-- DEFAULT 'XXX',
    emailid varchar(20)  UNIQUE,
    accountno int(11)
    );
  
> How to use AAD,DROP and MODIFY on column.
    DROP TABLE TAB1;

    ALTER TABLE tab ADD phone_no char(10);
    ALTER TABLE tab MODIFY phone_no char(12); --not working in postgres.
    ALTER TABLE tab1 MODIFY phone_no CONSTRAINT ph_nnull NOT NULL;
    ALTER TABLE tab ADD CONSTRAINT tab_uniqe UNIQUE(phone_no);

--modifying column in postgresql
    ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8) USING substr("MyColumn", 1, 8)

    ALTER TABLE tab DROP CONSTRAINT ph_unique;
--droping a column
    ALTER TABLE TAB DROP column phone_no;
--rename a column
    ALTER TABLE TAB1 RENAME COLUMN id to new_id;

    ALTER TABLE TAB ADD CONSTRAINT tab_pkey PRIMARY KEY (ID);
    alter TABLE tab add CONSTRAINT tab_pkey PRIMARY KEY (ID,NAME);
    ALTER TABLE TAB ADD CONSTRAINTS c_check CHECK(ID BETWEEN 101 AND 120);

    ALTER TABLE tab DROP constraint tab_pkey;
    ALTER TABLE TAB DROP PRIMARY KEY; --Not supporting in postgres

    ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber)

> In subqueries, which is efficient ,the IN clause or EXISTS clause? Does they produce the same result?

  EXISTS is efficient because,
  1.Exists is faster than IN clause.
  2.IN check returns values to main query where as EXISTS returns Boolean (T or F).
 
> Explain normalization with examples.

Normalization is a process of eleminating the redundancy and increasing the integrity.
Normal Forms: Review : -
Unnormalized ? There are multivalued attributes or repeating groups
    1 NF ? No multivalued attributes or repeating groups.
    2 NF ? 1 NF plus no partial dependencies
    3 NF ? 2 NF plus no transitive dependencies

Option 1: Make a determinant of the repeating group (or the multivalued attribute) a part of the primary key.

Option 2: Remove the entire repeating group from the relation. Create another relation which would contain all the attributes of the repeating group, plus the primary key from the first relation. In this new relation, the primary key from the original relation and the determinant of the repeating group will comprise a primary key.

Remove the attributes, which are dependent on a non-key attribute, from the original relation. For each transitive dependency, create a new relation with the non-key attribute which is a determinant in the transitive dependency as a primary key, and the dependent non-key attribute as a dependent.
        
> What is De-normalization and difference between normalization and De-normalization?

• De-normalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.
• Normalization is the process of De-composing a relation with anomalies into a well structured relation.

> What is difference between Co-related sub query and nested sub query?

•   Correlated sub query runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
•   Nested sub query runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

  For example,
  Correlated Subquery:
    select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno);

  Nested Subquery:
    select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno);
  
> What is the main difference between the IN and EXISTS clause in sub queries?
The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN : The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS : The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

> what is the ACID property in database. 
ATOMICITY-- Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged
CONSISTENCY -- The consistency property ensures that any transaction will bring the database from one valid state to another
ISOLATION -- Isolation refers to the requirement that other operations cannot access or see the data in an intermediate state during a transaction
DURABILITY --Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone.

> How to find out the database name(schema name) and tables from SQL*PLUS command prompt?
  select * from information_schema.tables
 
> What is output of following query? "Select 2 from employee";
   It depends upon number of rows in table. This query will print 2 as many times as rows in table.
 
> What is the use of CASCADE CONSTRAINTS?
1)-Cascade constraint is used with drop column clause.
2)-Cascade constraint drops all referential integrity constraint primary key and unique key from a multi column table.
3)-Cascade constraint also drop multi column constraint.

    Alter table Emp Drop (PK) cascade constraint;

> What is a default TCP/IP socket assigned for SQL Server?
   1433 is default tcp/ip socket sql server.
   5432 in case of postgresql.
   3306 in case of mysql

> How can i hide a particular table name of our schema.
    We can create in two ways:
    1) using synonym
    2) by using views.

synonym : an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

> What are two methods of retrieving SQL?
   1-select
   2-using cursor
   stored procedures