Author: Shameer
•8:02 AM
SQL - View

             A SQL View is a virtual table, which is based on SQL SELECT query. A view consists of rows and columns just like a table,except that the real tables store data, while the views won’t store data. The difference between a view and a table is that views are definitions built on top of a single table or multiple tables (or on top of another view), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables.

Views ensure the security of data by restricting access to the following data:

• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.

Some common examples of views are:
• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.

CREATING VIEWS

A view can be created by using the CREATE VIEW statement

SYNTAX

CREATE VIEW view_name [column_name (s)] AS
SELECT column_name(s)
FROM table_name
WHERE condition [WITH CHECK OPTION]

column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.

A view can be referenced and used from another view, from a SQL query, and from stored procedure. You can reference a view as you would reference any real SQL database table:

SELECT * FROM view_name

Example

Customer_Info


Product_Info


     CREATE VIEW V_PRODUCT_SALES AS
      SELECT A1.Customer_Name Customer_Info, SUM (A2.Sales) Product_Info
        FROM Customer_Info A1, Product_Info A2
      WHERE A1.Product_Name = A2.Product_Name
GROUP BY A1.Customer_Name

To find the contents of the VIEW

SELECT * FROM V_PRODUCT_SALES



UPDATING A VIEW

SYNTAX


CREATE OR REPLACE VIEW view_name AS
 SELECT column_name(s)
   FROM table_name
 WHERE condition


DROPPING A VIEW

SYNTAX

DROP VIEW view_name

Advantages:

1. A view hides the complexity of the database tables from end users.
2. Views take very little space to store as it is not storing the actual data.
3. A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying table.
4. A view can also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be retrieved using simple queries.

Restrictions imposed on views:

• A view can be created only in the current database.
• The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
• A view can be created only if there is a SELECT permission on its base table.
• A SELECT INTO statement cannot be used in view declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.
Author: Shameer
•10:37 AM
SQL - DISTINCT


The DISTINCT keyword can be used to return only distinct (different) values.

Main Table


1.   SELECT DISTINCT Customer_Name 
         FROM customer


2. SELECT DISTINCT Customer_Name FROM customer WHERE Cus_Id = 5


3.       SELECT DISTINCT Customer_Name 
             FROM customer 
     ORDER BY Customer_Name


4.       SELECT DISTINCT Customer_Name 
            FROM customer 
    ORDER BY Customer_Name DESC


5.     SELECT DISTINCT Customer_Name, Order_Date 
          FROM customer


6.     SELECT DISTINCT Customer_Name, Order_Date 
          FROM customer 
       WHERE Cus_Id = 4


7.         SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name, Order_Date



8.        SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name, Order_Date DESC



9.         SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name DESC, Order_Date DESC


Author: Shameer
•7:24 AM
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Main Table


1.     SELECT Customer_Name, SUM (Order_Price)
           FROM customer
    GROUP BY Customer_Name



2.      SELECT Customer_Name, AVG (Order_Price)
            FROM customer
    GROUP BY Customer_Name



3.      SELECT Customer_Name, SUM (Order_Price), Order_Date
            FROM customer
    GROUP BY Customer_Name, Order_Date



4.      SELECT Customer_Name, SUM (Order_Price) , Order_Date
           FROM customer
         WHERE Cus_Id=3
    GROUP BY Customer_Name, Order_Date



5.      SELECT Customer_Name, SUM (Order_Price) 
            FROM customer
    GROUP BY Customer_Name, Order_Date
    ORDER BY Customer_Name DESC


Author: Shameer
•11:38 AM

1. Download the current version of MySQL from http://dev.mysql.com. The Windows Essentials (x86) or Windows (x86) version can be used.

2. Double click setup.exe to launch the installer.

3. At the Welcome screen of the Setup Wizardclick Next




4. Choose the Typical option as the setup type and click Next.


5. Click Install


6. At this point you are prompted to create an account on the MySQL.com website. If you would like to do so, choose the appropriate option and follow the instructions.

Otherwise, choose the Skip Sign-up option and click Next.

7. The MySQL Server Database Engine is now installed. At this point the server must be configured, choose the Configure the MySQL Server now option and click Finish.


8. At the Welcome screen of the MySQL Server Instance Configuration Wizard, click Next.


9. Choose the Detailed Configuration option and click Next.


10. Choose the Server Machine option and click Next.



11. Choose the database usage type that best describes your installation. If you are unsure, choose the Multifunctional Database option and click Next.


12. Choose a location that will house the database tablespace and click Next. If the current machine will be used to store the Portfolio catalog, the default location will work fine.


13. This step of the Wizard allows you to help optimize the database for the number of concurrent connections that you expect to have to a Portfolio catalog.
If you are not sure how many concurrent users you will have, choose the Online Transaction Processing (OLTP) option and click Next.


14. Choose the Enable TCP/IP Networking option and click Next.



15. Choose the Best Support for Multilingualism option and click Next.



16. Extensis recommends running the MySQL server as a service. To do so, check the Install as a Windows Service option and choose a Service Name. It is also recommended to launch the MySQL Service automatically, choose the appropriate option and click Next.


17. In this step you choose enable the root user and choose a password. Check the Modify Security Settings option, then enter and confirm the new root user password. When finished, click Next.


18. Click Execute to configure the MySQL instance.


19. Click Finish to close the Wizard.




Author: Shameer
•10:09 AM
1. What is a Primary Key?
            A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications

2. What is a Foreign Key?
            A foreign key is a single column or a multiple columns defined to have values that can be mapped to a primary key in another table.

3. What is an Index?
            An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

4. What are NULL values?
             NULL represents no value
             NULL is not the same as an empty string ‘’
             NULL is not same as zero value (0)
             NULL can be used as any data type
             NULL should not be used in any comparison operators
             NULL has its own equality operator IS and not-equality operator IS NOT

5. Write a query to find the 5th details from a table
      SELECT column_name FROM table_name WHERE auto_incremented_id =
        (SELECT TOP 1 auto_incremented_id FROM
         (SELECT TOP 3 auto_incremented_id FROM employee ORDER BY auto_incremented_id ASC)
                 table_name ORDER BY auto_incremented_id DESC);



6. Write a Query to display the Top N rows ?

MySQL

SELECT column_name FROM table_name
LIMIT number
  
SQL Server

SELECT TOP number/percent column_name FROM table_name

Oracle

SELECT column_name FROM table_name
WHERE ROWNUM<= number

7. Write a Query to display the Last N rows ?


MySQL

SELECT column_name FROM table_name
ORDER BY auto_incremented_id DESC
LIMIT number

SQL Server

SELECT TOP number/percent column_name
FROM table_name
ORDER BY auto_incremented_id DESC

Note: The above query will display the result in descending order 

To display the result in ascending order

SELECT * FROM (SELECT TOP number/percent column_name
FROM table_name
ORDER BY auto_incremented_id DESC)
ORDER BY auto_incremented_id ASC

ORACLE

SELECT * FROM (SELECT column_name FROM table_name
ORDER BY auto_incremented_id DESC)
WHERE ROWNUM<= number
ORDER BY auto_incremented_id ASC

8. Write a Query to display the second highest salary ?

 SELECT MAX (empsal) FROM employee
  WHERE empsal < (
 SELECT MAX (empsal) FROM employee)

Explanation:


First the subquery will be executed. i.e.  SELECT MAX (empsal) FROM employee)  =  20000

Then the main query will be executed i.e.

 SELECT MAX (empsal) FROM employee
  WHERE empsal <  20000                            =    15000

9. Write a query to retrieve the duplicate rows in a table ?


     SELECT empname, empsal FROM employee
GROUP BY empname, empsal
    HAVING count(*) > 1



10. What is RDBMS?
Relational Data Base Management Systems (RDBMS) that maintains data records and indices in tables. Relationships may be created and maintained across and among the data and tables.


11.  What is SQL?
SQL stands for 'Structured Query Language', developed by IBM in 1970&#8217;s


12.  What is SELECT statement?
The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.


13.  What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases.


14.  What is the INSERT statement?
The INSERT statement allows us to insert information into a database.


15.  How do you delete a record from a database?
Using the DELETE statement we can remove records or any particular column values from a database.


16.  How to find the total number of records in a database table?
We can use the COUNT keyword for counting records, 
Example: SELECT COUNT(*) FROM Emp


17.  What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows.


18.  What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently.


19. What is GROUP BY?
GROUP BY keyword has been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.


20.  What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?
Dropping:&nbsp; (Table structure + Data are deleted), Invalidates the dependent objects, Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete: (Data alone deleted), Doesn&#8217;t perform automatic commit

21.  What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

22.  What are triggers? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

23.  What is a join and explain different types of joins?
Join is used in queries to explain how different tables are related. Joins also let us select data from a table depending upon data from another table.

Types of joins:
INNER JOINs, OUTER JOINs, CROSS JOINs. 
Note: OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.


24.  What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query.

25.  How to implement one-to-one, one-to-many and
many-to-many relationships while designing tables? One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. 

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

26.  What are the Properties of Sub-Query?
A sub query must be enclosed in the parenthesis.
A sub query must be put in the right hand of the comparison operator, and
A sub query cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries. 


27.  What are types of sub-queries? 
Single-row sub query, where the sub query returns only one row.
Multiple-row sub query, where the sub query returns multiple rows,. 
Multiple column sub query, where the sub query returns multiple columns.

28.  What are the different index configurations a table can have?

A table can have one of the following index configurations:

a) No indexes

b) A clustered index

c) A clustered index and many nonclustered indexes

d) A nonclustered index

e) Many nonclustered indexes


29.  What is the difference between a Local and a Global temporary table?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.









Author: Shameer
•10:28 AM
JOIN
The SQL JOIN clause is used whenever we have to select data from 2 or more tables. There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't specify INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short ‘INNER JOIN’ = ‘JOIN’. Different databases have different syntax for their JOIN clauses).

INNER JOIN
The INNER JOIN keyword return rows when there is at least one match in both tables.

SYNTAX

SELECT column_name FROM table_name_1
INNER JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
JOIN Student
ON Department.Dept_Id = Student.Dept_Id
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN (LEFT JOIN) and RIGHT OUTER JOIN (RIGHT JOIN).

LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name_1), even if there are no matches in the right table (table_name_2).
SYNTAX

SELECT column_name FROM table_name_1
LEFT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department



Table 2: Student



OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
LEFT JOIN Student
ON Department.Dept_Id = Student.Dept_Id


RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table_name_2), even if there are no matches in the left table (table_name_1).

SYNTAX

SELECT column_name FROM table_name_1
RIGHT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
RIGHT JOIN Student
ON Department.Dept_Id = Student.Dept_Id


FULL JOIN
The FULL JOIN returns all the rows from the left table, and all the rows from the right table.

SYNTAX

SELECT column_name FROM table_name_1
FULL JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
FROM Department
FULL JOIN Student
ON Department.Dept_Id = Student.Dept_Id