Basics of SQL

1.What is Database :

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content 

2.What is a table :

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees , and a set of columns.

basic syntax to create table : 

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... column_n datatype [ NULL | NOT NULL ] );

Create table Murthy (sno number,name varchar2);

 

3.What is view :

A view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables

Syntax to create a view : 
 
CREATE VIEW emp_view AS SELECT last_name, salary*12 annual_salary FROM employees WHERE department_id = 20;

4.What is join and types of joins :

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• FULL JOIN: Return all rows when there is a match in ONE of the tables

Note :
LEFT JOIN is same as LEFT OUTER JOIN
RIGHT JOIN is same as RIGHT OUTER JOIN

5. Oracle Constraints :

• Not null

• Unique Constraint

• Primary Key

•  Foreign key

• Check constraint

A NOT NULL constraint prohibits a database value from being null.

A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

A foreign key constraint requires values in one table to match values in another table.

A check constraint requires a value in the database to comply with a specified condition.

A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references. 

6.What is the difference between primary key and unique key

Unique Key	Primary Key
A table can contain more than one unique key	A table can contain only one primary key (can be one/more columns)
Unique key allows one null value	Primary key will not allow null values

7.What are DDL and DML commands .

DDL (DataDifinition Language)	DML (Data Manipulation Language)
Create	Select
Alter	Insert
Truncate	Update
Drop	Deletet

8. What is the difference between “delete” , “truncate” and “drop” commands

 

Delete Command	Truncate Command	Drop Command
Delete Command Belongs to DML	Truncate Command Belongs to DDL	Drop Command Belongs to DDL
Can be Used to delete entire Table data	Can be Used to delete entire Table data	Can be Used to delete entire Table data and also the structure
Can be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause
Can be rolled back	Can't be rolled back	Can't be rolled back

 

9. Relation between views and tables

Tables are the actual database entities that hold your rows.

Views are "imaginary" tables that are constructed based on the actual tables.

A table contains data, a view is just a SELECT statement which has been saved in the database
The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.

Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

Syntax : 

CREATE VIEW view_name AS SELECT columns
  FROM tables WHERE conditions;

10. What are function,trigger and stored procedures :

A stored procedure is a user defined piece of code written in the local version of PL/SQL, which may return a value that is invoked by calling it explicitly.

A trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

A function is a user defined piece of code and it should return a value making it a function.

Syntax : 
Procedure : 
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
   [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

11. What is the purpose of ‘distinct’ in sql

Distinct is used to display unique values

Ex:

Select distinct from

Select distinct ename from emp

12. What is the difference between union and union all

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).