Explore topic-wise InterviewSolutions in .

DDL -Data Definition Language commands create database objects such as tables, views, etc., The various DDL commands are Create Table, Alter Table, Create View, Drop Table.
-Create Table
SYNTAX:
CREATE TABLE table_name
( field1 datatype [ NOT NULL ],
field2 datatype [ NOT NULL ],
field3 datatype [ NOT NULL ]…)
An example of a CREATE TABLE statement follows.
SQL> CREATE TABLE BILLS (2 NAME CHAR(30),

3 AMOUNT NUMBER,
4 ACCOUNT_ID NUMBER);

The ALTER TABLE command is used to do two things:

• Add a column to an existing table
• Modify a column that already exists

SYNTAX:

ALTER TABLE table_name ADD( column_name data_type);
ALTER TABLE table_name MODIFY (column_name data_type);
The following command changes the NAME field of the BILLS table to hold 40 characters:
SQL> ALTER TABLE BILLS MODIFY (NAME CHAR(40));

DROP command is used to remove the entire table from the database.
syntax:
DROP TABLE tablename;
Example:
DROP TABLE student;

The operator AND means that the expressions on both sides must be true to return TRUE. If either expression is false AND returns FALSE.
Syntax:
SELECT column1, column2, columnN FROM table_name
WHERE condition1 AND condition2 … AND conditionN;

Example 1:
It would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000 AND age is less than 25 years:
SQL> SELECT ID, NAME, SALARY FROM

CUSTOMERS WHERE SALARY > 2000 AND age < 25;

Example 2:
To find out which employees have been with the company for 5 years or less and have taken more than 20 days leave,:
SQL> SELECT empNAME FROM VACATION WHERE YEARS <= 5 AND LEAVETAKEN > 20;

When executing an SQL command for any RDBMS, the SQL engine interprets the task for execution. There are various components included in the process. These components of SQL architecture are Query Dispatcher, Optimization Engines, Classic Query Engine, and SQL Query Engine, etc.,

It is single row and single column dummy table provided by oracle.

• Numeric data type is classified as exact numeric data types and floating-point numeric data types.
• Character, string data types is classified as char and varchar data types.

The commit command is used to save the transactions entered into the table.

The drop command is used to remove/delete tables,
syntax:
DROP TABLE tablename;

The SQL is expanded as ‘Structured Query Language’.

The update command is used to change row values from a table. The SET keyword takes the column in which values needs to be changed or updated.

To view All the Columns and all the Rows (Entire Table values)
> SELECT * FROM student;

The various SQL operators are Arithmetic operators, Comparison operators, Logical operators, Operators used to negate conditions.

Syntax:
SELECT DISTINCT columnname FROM tablename;

The logical operators in SQL are ALL, AND, ANY BETWEEN, EXISTS, IN, LIKE, NOT, OR, IS NULL, UNIQUE.

A field with a value of NULL means that the field actually has no value stored in it.

A view is referred to as a virtual table. Views are created by using the CREATE VIEW statement.

1. SQL ORDER BY Clause:

The SQL ORDER BY Clause is used in a SELECT statement to sort results either in ascending or descending order.

Syntax for using SQL ORDER BY clause to sort data is:
>SELECTcolumn-list FROM table_name ORDER BY column1, column2,.. columnN [DESC]];
For Example:
If you want to sort the employee table by salary of the employee, the SQL query would be.
> SELECT name, salary FROM employee ORDER BY salary;
By default, the ORDER BY Clause sorts data in  ascending order. If data to be sorted in descending order, the command would be as given below.
> SELECT name, salary FROM employee ORDER BY name, salary DESC;

2. SQL GROUP BY Clause:
The SQL GROUP BY Clause is used with the group functions to retrieve data grouped according to one or more columns.

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.
SELECT column1, column2 FROM table name WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

For Example:

If you want to know the total amount of salary spent on each department, the query would be:
> SELECT dept, SUM (salary) FROM employee GROUP BY dept;
The group by clause should contain all the columns in the select list expect those used along with the group functions.
> SELECT location, dept, SUM (salary) FROM employee GROUP BY location, dept;

Syntax:
CREATE TABLE tablename (columnnamel datatype(size), columnname2 datatype(size) …);

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.

Check Constraint at column level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
gender char(l),
salary number(lO) CHECK (salary >= 5000 AND salary <= 40000),
location char(10)

s);
In the above example, the salary column is defined as a number column with a check constraint. It checks constraint checks the value that can be stored in the salary column should be greater than or equal to 5000 which is the minimum value and the value is less than or equal to 40000 which is the maximum value for the column salary.

SQL Constraints are rules used to limit the type of data that can be stored into a table, to maintain the accuracy and integrity of the data inside table.
Constraints can be divided into two types,

• Column level constraints: limits only column data
• Table level constraints: limits whole table data

Constraints are used to make sure that the integrity of data is maintained in the database. The NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT are the most used constraints that can be applied to a table.

1. NOT NULL Constraint:

NOT NULL constraint restricts a column from having a NULL value. Once *NOT NULL* constraint is applied to a column, you cannot store null value to that column. It enforces a column to contain a proper value.
Example using NOT NULL constraint
CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);

2. UNIQUE Constraint:

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data.
Example using UNIQUE constraint when creating a Table
CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

3. Primary Key Constraint:

The primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain a null value.
Example using PRIMARY KEY constraint
CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

4. Foreign Key Constraint:

FOREIGN KEY is used to relate two tables. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Example using FOREIGN KEY constraint at Table Level
CREATE TABLE Orders
(
O-Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)

)

5. CHECK Constraint:

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database.
Example using CHECK constraint
create table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);

The Privileges defines the access rights given to a user on a database object. There are two types of privileges.

• System privileges – This allows the user to CREATE, ALTER, or DROP database objects.
• Object privileges – This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.

Few CREATE system privileges are listed below:

CREATE object – allows users to create the specified object in their own schema.
CREATE ANY object – allows users to create the specified object in any schema.

Few of the object privileges are listed below:

• INSERT – allows users to insert rows into a table.
• SELECT – allows users to select data from a database object.
• UPDATE – allows user to update data in a table.
• EXECUTE – allows user to execute a stored procedure or a function.

Roles:
Roles are a collection of privileges or access rights. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if roles are defined, one can grant or revoke privileges to users, thereby automatically granting or revoking privileges.

Some of the privileges granted to the system roles are as given below:

1. CONNECT – CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE SESSION, etc.

2. RESOURCE – CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIG-GER, etc.

The primary usage of the RESOURCE role is to restrict access to database objects. DBA- ALL SYSTEM PRIVILEGES.

Boolean expressions return rows (results) when a single value is matched. Boolean expressions commonly used in a WHERE clause are made of operands operated on by SQL operators.

For example,

> SELECT * FROM EMPLOYEES WHERE AGE = 45;
The above statement returns all those records (rows) whose age column is having the exact value of 45 from the employees table.