SQL Views

SQL Modifying View

SQL Join View

SQL Inline View

An SQL View is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

The maximum number of columns that can be defined in a SQL View are 1000 as in tables.

The reasons for using views (benefits / advantages of views) in applications can be many like;

• Reducing complexity.
• Improving security.
• Renaming the table columns.

Use Create View statement to create a view:

SQL> create view emp_info as
2 select first_name ' ' last_name Name, employee_id
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)

SQL> select * from emp_info;

Name	Employee_ID
Michael Hartstein	201
Pat Fay	202

You can also define altogether a new column in a view, as column commission below:

SQL> create view emp_info(Name, employee_id, commission) as
2 select first_name ' ' last_name ,employee_id, salary * .01
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)
COMMISSION	NOT NULL	NUMBER

SQL> select * from emp_info;

Name	Employee_ID	COMMISSION
Michael Hartstein	201	130
Pat Fay	202	60

You can also create Read-Only views. On the read-only views no DML operations can be performed.

Create view emp_info(Name, employee_id, commission) as
select first_name ' ' last_name ,employee_id, salary * .01
from employees where department_id = 20
With Read only;

SQL Having

SQL Group By

Examples of SQL Group By and SQL Having

Null in SQL Group By

The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.

Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.

A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.

According to Wikipedia (http://en.wikipedia.org) HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

An SQL statement with the HAVING clause may or may not include the GROUP BY clause.

HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

Example 1 of SQL Having

Find the average salary of for each department that has either more than 1 employee or starts with a “To”:

SELECT Dept, AvgSal=(AVG(Salary))
FROM Employee
GROUP BY Dept
HAVING COUNT(Name) > 1 OR Dept LIKE “To”

Example 2 of SQL Having

Workforce (workforceno, name, position, salary, email, dcenterno)

For each distribution center with more than one member of workforce, find the number of workforce working in each of the centers and the sum of their salaries.

SELECT dCenterNo, COUNT(workforceNo) AS totalworkforce,
SUM(salary) AS totalSalary
FROM workforce
GROUP BY dCenterNo
HAVING COUNT(workforceNo) > 1
ORDER BY dCenterNo;

Important points about SQL Having:

Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.

Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.

Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

SQL Sub Queries

SQL inline View subquery

SQL Correlated subquery

SQL Scalar subquery

Subquery is a query inside a main query.

SQL subquery can be embedded:

• As a column expression in the main SQL statement
  
• As a filter inside the WHERE (or HAVING) clause in the main SQL statement
  
• As a datasource inside the FROM clause in the main SQL statement

While working with SQL Subqueries you must:

• Enclose the subquery in parentheses
  
• Do not use a semicolon at the end of the subquery statement

Example 1 of subquery

Find the names of all of the managers in the Employees table.

Employees (emp_id, Emp_name, ManagerID)

SELECT Emp_name AS Employee
FROM Employees
WHERE emp_id IN (SELECT DISTINCT ManagerID FROM Employees)

Example 2 of SQL Subquery

Store_Info (store_name, sales, date)
Location (region_names, store_name)

Find the sales of all stores in the East region.

SELECT SUM(Sales) FROM Store_Info
WHERE Store_name IN
(SELECT store_name FROM Location
WHERE region_name = 'East')

You can nest any number of such queries; Oracle does not have a limit.

There is also another term used NESTED SUBQUERIES. when you use subqueries in the WHERE clause of the SELECT statement it is called nested subquery. There are only 255 levels of subqueries.

SQL Groups

SQL Having

Examples of SQL Group By and SQL Having

Null in SQL Group By

It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

store_name	Sales	Date
London	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
London	$300	Jan-08-1999
Boston	$700	Jan-08-1999

First, we need to make sure we select the store name as well as total sales.

SELECT store_name, SUM (Sales)
FROM Store_Information

Second, we need to make sure that all the sales figures are grouped by stores.

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

So the final query is:

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

The result is:

store_name	SUM(Sales)
London	$1800
San Diego	$250
Boston	$700

Example 2 of SQL Group BY

SELECT COUNT (*) FROM t_state

The above statement returns the total number of rows in the table. We can use GROUP BY to count the number of offices in each state.

With GROUP BY, the table is split into groups by state, and COUNT (*) is applied to each group in turn.

SELECT state, COUNT (*)
FROM t_state
GROUP BY state;

Important points to remember:

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt
From emp
Group by department

Correct way is:

Select deptno as department, count (*) as cnt
From emp
Group by deptno


What is the difference between the outputs of the following two queries?

Statement 1:

SELECT COUNT (*), SUM (comm)
FROM hr.employees;

Statement 2:

SELECT COUNT (comm), SUM (comm)
FROM hr.employees;

The COUNT (*) will count all rows in the table.

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

Restriction on SELECT Lists with Aggregation

If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.

SQL Joins

You will get vast amount of data/ information on this page regarding Oracle SQL JOINS like

1. Example of Simple SQL Join


2. SQL Outer Join


3. Example of SQL Outer Join


4. SQL Inner Join


5. Example of SQL inner Join


6. SQL Self Join


7. Example of SQL Self Join


8. SQL Cross Join


9. SQL Equijoin


10. Join using MULTIPLE TABLES

You will get good information on Joins from the book Effective Oracle by Design by Thomas Kyte

http://www.amazon.com/Effective-Oracle-Design-Osborne-ORACLE/dp/0072230657

If you are interested in DBMS Normal forms Please go to

http://www.bkent.net/Doc/simple5.htm

Here you will find an article on DBMS Normal forms named "A Simple Guide to Five Normal Forms in Relational Database Theory" which you will surely find useful.

SQL Group BY Examples

Example 1 of SQL Group By

Let us say we have a table name Orders.

Orders (O_Id, OrderDate, OrderPrice, Customer)

we want to find the total sum (total order) of each customer.

SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer

Source: http://www.w3schools.com/sql/sql_groupby.asp


Example 2 of SQL Group By

Let us say we have a table name Sales.

Sales(OrderID, OrderDate, OrderPrice, OrderQuantity, CustomerName)

We want to retrieve a list with unique customers from our Sales table, and at the same time to get the total amount each customer has spent in our store.

SELECT CustomerName, SUM(OrderPrice)
FROM Sales
GROUP BY CustomerName

Source: http://www.sql-tutorial.com/sql-group-by-sql-tutorial/


Example 3 of SQL Group By

Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.

SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID

Source: http://en.wikipedia.org/wiki/Group_by_(SQL)


Example 4 of SQL Group By

From Sells(bar, beer, price) find the average price for each beer

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;

Source: infolab.stanford.edu/~ullman/fcdb/aut07/slides/ra-sql2.ppt


Example 5 of SQL Group By

You could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Source: http://www.techonthenet.com/sql/group_by.php

SQL Delete

How to delete all records from a table?

Delete from dept;

How to delete specific records from a table?

Delete from emp where empno=20;

How to delete duplicate records from the table?

Suppose we have a table t1(id integer, name varchar(10))

select * from t1;

id	name
1	aaa
2	bbb
3	bbb
4	ccc
5	ccc
6	ddd

delete from t1

where id not in ( select min(id)

from t1

group by name )

Few Examples on SQL Delete:

BOTH THE BELOW EXAMPLES OF UPDATE AND DELETE USE CORRELATED SUBQUERIES:

We need to update sal of all the employees in the emp table to the maximum salary in the corresponding dept.

UPDATE emp e1

SET sal = (SELECT MAX(sal)FROM emp e2

            WHERE e1.deptno = e2.deptno);

We need To delete the records of all the employees in the emp table whose sal is below the average sal in the department

DELETE FROM emp e

WHERE sal < (SELECT AVG(sal) FROM emp

                     WHERE deptno = e.deptno);

SQL Interview Question

What Is SQL?
SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.

What is the difference between database & relational database.

Database or Database Management System(DBMS) and Relational Database Management System(DBMS) are both used by SQL to store data and structures. However, each type of Database Management System is preferred with respect to different uses.

The main difference between the two is that DBMS saves your information as files whereas RDMS saves your information in tabular form. Also, as the keyword Relational implies, RDMS allows different tables to have relationships with one another using Primary Keys, Foreign Keys etc. This creates a dynamic chain of hierarchy between tables which also offers helpful restriction on the tables.

DBMS sorts out its tables through a hierarchal manner or navigational manner. This is useful when it comes to storing data in tables that are independent of one another and you don’t wish to change other tables while a table is being filled or edited.

What are different categories of SQL commands?

SQL command falls into following four categories:

• DML (Data Manipulation Language) which provides data manipulation features
• DDL (Data Definition Language) which is used to manipulate database structures
• TCL (Transaction Control Language) that takes in charge data transaction verification and error handling
• DCL (Data Control Language) are security statements that feature user restrictions and data access permissions to promote security of your data.

What is the Basic Structure of an SQL?

SQL is framed upon the structure of relational operations. It is based on certain modifications and enhancements.

A very basic SQL query form is: select A1, A2, ..., An from R1, R2, ..., Rm where P;

Here An are attributes, Rm is the relations within the database and P is the predicate or filter.

Define SELECT, INSERT, CREATE, DELETE, UPDATE, DROP keywords

SELECT keyword is used to highlight and get entries in rows from tables or views. It can also be accompanied by AS keyword to provide an alias. To filter the SELECT statement, WHERE clause may be included to provide filter conditions and select only the wished entries that satisfy the condition.

 

INSERT allows to add or insert a row or multiple rows in a database table. Accompanied by VALUES keyword lets you add a row with specific values. INSERT may also be accompanied with SELECT to insert the preselected row.

CREATE is a keyword used to create elements in SQL. It is usually accompanied with the keyword to be created such as CREATE DATABASE, CREATE TABLE, CREATE VIEW, etc.

DELETE keyword is used to deletes record(s) in a database. You should always use it carefully to avoid unwanted data loss. You may delete records you didn’t want to delete. Use WHERE clause to specify the range of the records you wish to delete.

UPDATE keyword updates or changes the existing data within an existing record. Be sure to note that the record must be existent.

DROP keyword drops or deletes a table within the database.

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

RUN

What are the key differences between SQL and P/L SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early ’90s. It adds procedural features of programming languages in SQL.

What is data definition language?

DDL or Data Definition Language pertains to the SQL commands directly affecting the database structure. DDL is a category of SQL command classifications that also include DML (Data Manipulation Language), Transactions, and Security. A particular attribute of DDL commands is statements that can manipulate indexes, objects, tables, views, triggers, etc. Three popular DDL keywords in SQL are:

CREATE – which is used to create a table

CREATE TABLE tableName (name data_type);

ALTER – used to modify entries or existing columns within a table.

ALTER TABLE tableName [additional syntax such as ADD, DROP, MODIFY]

DROP – used to Delete or Drop an existing table along with its entries, constraints, triggers, indexes, and permissions. Essentially deletes the table.

DROP TABLE tableName;

What is Data Manipulation Language?

DML or Data Manipulation Language is a set of commands that are classified pertaining to its capability to give users permission to change entries within the database. This may be through Inserting, Retrieving, Deleting or Updating data within tables. Popular DML statements arise from these core functionalities and are listed below:

 

SELECT – used to highlight a row within a table and retrieve it.

SELECT [columnName] FROM [tableName]

UPDATE – used to update entries from existing tables.

UPDATE [tableName] SET [value]

INSERT – used to insert entries into an existing table.

INSERT INTO [tableName]

DELETE – used to delete entries from an existing table

DELETE FROM [tableName]

What is Transaction Control Language (TCL)?

TCL is a category of SQL commands which primarily deals with the database transaction and save points. These keywords implement the SQL functions and logic defined by the developer into the database structure and behavior. Examples of these TCL commands are:

COMMIT – used to commit a transaction

ROLLBACK – in any advent of errors, transaction rollback is invoked by this keyword.

SAVEPOINT – keyword representing the reverting point of rollback

SET TRANSACTION – sets the specifics of the transaction

What is Data Control Language (DCL)?

Data Control Language or DCL oversees the issuance of access and restrictions to users, including the rights and permissions required within the SQL statements. Example DCL keywords are:

GRANT – DCL keyword that provides access to certain databases to users.

REVOKE – opposite of the GRANT keyword. Revokes or withdraws the privileges given to the user.

Define tables and fields in a database

In terms of databases, a table is referred to as an arrangement of organized entries. It is further divided into cells which contain different fields of the table row.

A field pertains to a data structure that represents a single piece of entry. They are then further organized to records. They practically hold a single piece of data. They are the basic unit of memory allocation for data and is accessible.

What are different types of keys in SQL?

Keys are a vital feature in RDMS, they are essentially fields that link one table to another and promote fast data retrieval and logging through managing column indexes.

Different types of keys are:

Primary Key – a unique key that identifies records in database tables. By unique it means that it must not be Null and must be unique in the table.

Candidate Key – a unique field which identifies for column or group of columns independently, without any required reference to other fields.

Alternate Key – can be substituted in use for Primary Keys but are considered as a secondary. The difference is that Alternate Keys can have a Null value, provided that the columns have data within them. A type of Candidate Key which is also required to be unique.

Unique Key – Keys that offer restriction to prevent duplicate data within rows except for null entries.

The other keys available are Foreign Keys, Super Keys, and Composite Keys.

Name the different types of indexes in SQL and define them.

Unique Index: Prevents duplicate entries within uniquely indexed columns. They are automatically generated if a Primary Key is available.

Clustered Index: Used to organize or edit the arrangement within the table, with respect to the key value. Each table is only allowed to have a single clustered index only.

NonClustered Index: Conversely, NonClustered Index only manages the order of logic within entries. It does not manage the arrangement and tables can have multiple NonClustered Indexes.

How can you call a PL/SQL procedure from SQL? 

By use of the EXECUTE (short form EXEC) command.

Which is the subset of SQL commands used to manipulate Oracle Database structures?

Data Definition Language (DDL)

What is UNION and UNION ALL keyword in SQL and what are their differences?

The UNION operator in SQL combines multiple sets highlighted in the SELECT statements. The restrictions of the set are: (1) column number must be identical, (2) Data Types in the set must be the same, and (3) the order of the column highlighted in the SELECT statement must be the same. It automatically eliminates duplicate rows within the results highlighted in the SELECT statement.

UNION ALL does the same function as the UNION, but it includes all, including the duplicate rows.

SELECT C1, C2 FROM T1

UNION

SELECT Cx, Cy FROM T2;

What are the different types of joins in SQL?

The join keyword queries entries from multiple tables. It is used with different keys to find these entries and is conscious on the link between fields.

Inner Join: Returns rows which are common between the tables

Right Join: Returns rows of the right-hand side table, including the common rows.

Left Join: Returns rows of the left-hand side table, including the common rows.

Full Join: Returns all rows, regardless if common or not.

 

Write a query to select the second highest salary from a table.

SELECT max(salary) AS salary_2 FROM emp WHERE salary < (SELECT max(salary) AS salary_1 FROM emp) 

Write a query to select the 5th highest salary from a table.

SELECT min(salary) AS high5 
FROM employee 
WHERE salary IN(SELECT DISTINCT TOP 5 salary FROM employee ORDER BY salary DESC) 

How to find duplicate records with the number they are duplicated?

SELECT Id, count (*) as num_records
from table 
group by id 
having count (*) > 1

What is the difference among UNION, MINUS and INTERSECT?

The UNION keyword is used in SQL for combining multiple SELECT queries but deletes duplicates from the result set.

The INTERSECT keyword is only used for retrieving common rows using SELECT queries between multiple tables.

The MINUS keyword essentially subtracts between two SELECT queries. The result is the difference between the first query and the second query. Any row common across both the result set is removed from the final output.

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

 REVOKE

How do you execute a host operating system command from within SQL? 

 By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command. 

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

 CREATE TABLE .. AS SELECT command

What is the difference between Delete and Truncate command in SQL? 

Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. TRUNCATE is a DDL command whereas DELETE is a DML command. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate. 

How can variables be passed to a SQL routine?

By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: “select * from dba_tables where owner=&owner_name;” . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

What SQLPlus command is used to format output from a select? 

This is best done with the COLUMN command. 

Can Primary key is a Foreign Key on the same table? 

Yes 

You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done? 

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:

 

select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);

What is the use of the DROP option in the ALTER TABLE command? 

It is used to drop constraints specified on the table. 

What is a Cartesian product? 

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

How do you prevent output from coming to the screen? 

The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

What is the use of CASCADE CONSTRAINTS? 

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Which function is used to find the largest integer less than or equal to a specific value? 

FLOOR 

What is a PRIMARY KEY? 

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table. 

What is a FOREIGN KEY?

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

What is a UNIQUE KEY? 

A UNIQUE KEY is one or more columns that must be unique for each row of the table.

What is the difference between UNIQUE and PRIMARY KEY? 

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <null> value is also restricted.

Consider the below DEPT and EMPLOYEE table and answer the below queries. 

DEPT DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)), LOC (VARCHAR2(13)

EMPLOYEE EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)), JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE), SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2)) MGR is the EMPno of the Employee whom the Employee reports to. DEPTNO is a foreign key. 

List all the Employees who have at least one person reporting to them. 

SELECT ENAME FROM EMPLOYEE WHERE EMPNO IN (SELECT MGR FROM EMPLOYEE); 

List the highest salary paid for each job. 

SELECT JOB, MAX(SAL) FROM EMPLOYEE GROUP BY JOB 

In which year did most people join the company? Display the year and the number of Employees.

SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY'));

Write a correlated sub-query to list out the Employees who earn more than the average salary of their department.

SELECT ENAME,SAL
FROM EMPLOYEE E
WHERE SAL > (SELECT AVG(SAL)
FROM EMPLOYEE F
WHERE E.DEPTNO = F.DEPTNO);

Find the nth maximum salary.

SELECT ENAME, SAL
FROM EMPLOYEE A
WHERE &N = (SELECT COUNT (DISTINCT(SAL))
FROM EMPLOYEE B
WHERE A.SAL<=B.SAL);  

Select the duplicate records (Records, which are inserted, that already exist) in the EMPLOYEE table.

SELECT * FROM EMPLOYEE A
WHERE A.EMPNO IN (SELECT EMPNO                                         
FROM EMPLOYEE                                         
GROUP BY EMPNO                                          
HAVING COUNT(EMPNO)>1)
AND A.ROWID!=MIN (ROWID));

Write a query to list the length of service of the Employees (of the form n years and m months).

SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN
(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
FROM EMPLOYEE;