Structured Query Language

A DBMS requires a query language to enable users to access data. SQL (Structured Query
Language) is the language used by most relational database systems.

SQL commands are categorized under following sections:

i) DDL (Data Definition Language)
ii) DML (Data Manipulation Language)
iii) TCL (Transaction Control Language)
iv) Session Control Commands.
v) System Control Commands.

DDL (Data Definition Language) – The commands which are used to create and maintain
database are grouped into the class called DDL. Create Table, Drop Table, Alter Table, Grant
privileges etc. comes under DDL.

DML (Data Definition Language) – The commands which are used to manipulate data in one
form or another are grouped into the class called DML. Insert into, Select, Update, etc. comes
under DML.

TCL (Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT are the examples
of TCL commands.

The table has following properties:

➢ Table can be created at any time.
➢ There is no need to specify the size of table.
➢ Table structures can be modified at any time.
➢ Tables may acquire more space automatically if the initial size is filled up.

Rules to name a Table:-

➢ The name must begin with a letter A-Z or a-z.
➢ It may contain letters, digits and special character underscore( _ ), $ and # but the use of $
& # is always discouraged.
➢ You can use either uppercase or lowercase letters both are same.
➢ The length of table name is upto 30 characters.
➢ The name must not be a SQL reserved word.

CREATE TABLE:- It is used to create a new table.

Syntax: CREATE TABLE table_name
( column_name type(size),
column_name type(size),
………);

Ex. To create a table STUDENT with Roll, Name, Address fields.

CREATE TABLE Student
( Roll Number(2), Name char(20), Address char(20));

Data Type: means the type of value it is going to take. Data types are char(w), integer,
number(w), number(w,p), date.

The Integrity Constraints:

Constraints are used to enforce rules at table level whenever row is inserted, updated or
deleted from the table. The constraints are used to prevent invalid data entry into tables.
Constraints can be defined at one of the two levels:
i) Column Level
ii) Table Level
Constraints are:
The NOT NULL constraint – This constraint ensures that the null values (empty values)
are not permitted for a specified column.
The DEFAULT Constraint – A column may be given a default value through DEFAULT
option. This prevents null values in the default column and inserts the default value
whenever user enters null in that column.
The UNIQUE Constraint – This is used to designate a column as a unique value. No two
rows in the table can have the same value for that key.
The PRIMARY KEY Constraint – Primary key is also used to enforce uniqueness. There is
only one primary key per table.
The CHECK Constraint – This explicitly defines a condition that each row must satisfy.
The TABLE Constraints – When a constraint is applied on a group of columns of the table,
it is called table constraint.

ALTER TABLE:- is used to change the structure of the table( add new column, change the
data type of column and drop any constraint using the alter command.)

The ADD Clause – Using this, one can add a new column into the table.
ALTER TABLE STUDENT ADD(marks number(5,2));

The MODIFY Clause – is used to modify the definition of an existing column.

ALTER TABLE STUDENT MODIFY(marks number(3));
ALTER TABLE STUDENT MODIFY(marks number(3) NOT NULL);
The Constraint can be applied using MODIFY clause of ALTER TABLE. But the column must
not contain any values in it.

DROP TABLE – To remove the table physically.

DROP TABLE student;
It is used to delete the table structure with the data in it.

DML (Data manipulation language) It provides statements for making changes(manipulating)

in the database. It includes the command insert, delete and modify.

WHERE Clause – It is used to put a condition.

The INSERT INTO – is used to add a new record to the end of an existing database.
INSERT INTO student VALUES(10, “Gaurav”, “New Friends colony”);
INSERT INTO student(RN, NAME) VALUES(3, “Kavita”);

INSERT INTO command can also be used through parameter substitution. In this, the numeric
column name is used with & (ampersand) sign as it is (eg., &rn) and character column name is
used in single quotation marks with & (eg. ‘&name).
INSERT INTO student VALUES (&rn, ‘&name’,’&address’);

The DELETE command – is used to delete rows of a table.

DELETE FROM student; it deletes all the rows of the student relation.
DELETE FROM student WHERE class= “XII”; this will delete all the rows which
have XII in the column class.

The UPDATE command – to change the value of any row in the table.
UPDATE t_name SET column1=value1 WHERE column=value;
UPDATE student SET marks=90 WHERE name= “Gaurav”;

SELECT Command – it retrieves information from one or more tables.

SELECT * FROM Student; /*it displays all the information with all the fields from
table student.*/
 SELECT name, rollno FROM student; /*it displays all the information with the fields
rollno and name from the*/ /*table
student. */

GROUP BY Clause – This groups the rows in the result table by columns that have the same
values, so that each group is reduced to a single row. Each item is separated by a comma.
SELECT COUNT(*), SUM(salary) FROM employee GROUP BY city;

HAVING Clause – it restricts grouped rows that appear in the result table.
SELECT COUNT(*), SUM(salary) FROM employee GROUP BY city HAVING city =
“New Delhi”;

ORDER BY Clause – it determines the order of rows in the result table. Data is placed in the
rows in ascending (ASC, by default) order or descending (DESC) order.
SELECT * FROM employee ORDER BY name;

CONDITION BASED ON PATTERN MATCHES – LIKE Clause – SQL provides a string

matching pattern. SQL provides two wildcard characters.
i) % (percent) – for matching any no. of characters.
ii) _ (underscore) – for matching a fixed no. of characters.
SELECT * FROM employee WHERE name LIKE “A%”; where name begins with A
SELECT * FROM employee WHERE name LIKE “A_ _ _ ”; where name begins with A and
have 4 characters.

To CHECK OR SEARCH with Null values – IS NULL

SELECT * FROM student WHERE stream IS NULL;
It will display all the rows or records that has empty value in the stream column.

Putting text in the Query statement – The query output must include some text with it. So to do
so, add text in single quotation mark in the query. For example,
SELECT NAME, ‘Has Scored’, MARKS/5 “PERCENTAGE”, ‘%’ FROM student;

Concatenating Strings – To concatenate strings, use || (double pipeline character). || is known

as concatenation operator. For example,
SELECT Fname || Lname FROM Student;

DISTINCT Clause – it is used to remove duplicate rows from the output.

SELECT DISTINCT City FROM employee;

CONDITION BASED ON RANGE - BETWEEN Clause – is used to specify the range of the
column.
SELECT name, city FROM employee WHERE salary BETWEEN 10000 AND 15000;

CONDITION BASED ON LIST - IN Clause – it is used to specify a condition based on a list.

The IN operator selects values that matches any value present in the list of values.
SELECT name FROM employee WHERE city IN (“Delhi”, “Bombay”);
this will display all the names of the employees who are working in either Delhi or Bombay.

Logical Operators : AND, OR, NOT.

Aggregate Functions are group functions.

1. AVG
2. SUM
3. MAX
4. MIN
5. COUNT
CREATE VIEW: A view is a virtual table with no data but can be operated like any other table.
It is used to get the data from the other table (known as base table).
CREATE VIEW St
AS SELECT Name FROM student
WHERE result= “Pass”;
So, view St will have all the names of the students whose result is pass. And to display it, use
the command select:
Select * from St;

COLUMN ALIASES : It is used to give a different name to the column in the query output. For
example,
SELECT Rno, Name, Marks/5 “PERCENTAGE” FROM student;

Functions: (Refer book for these functions)

NVL ( ), CONCAT( ), INITCAP( ), LOWER( ), UPPER( ), LPAD( ), RPAD( ), SUBSTR( ),
LTRIM( ), RTRIM( ), INSTR( ),
LENGTH( ), MOD( ), POWER( ), ROUND( ), SQRT( ), TRUNC( )

Some SQL Definitions:

1. Database – It is defined as a collection of interrelated data stored together to serve

many applications.
2. Entity – An entity is an object that can be distinctly identified. For example, a student in
a class with roll no, roll no is an entity.
3. Relation – A relation is a table which consists of rows and columns. A relation can be
viewed as a file in the database.
4. Tuple – Each row in a relation is called a record or a tuple.
5. Domain – The domain is a set of all possible values that an attribute can have.
6. Data Redundancy – Duplication of Data.
7. Cardinality –It refers to the number of rows/ tuples in a relation.
8. Degree of a relation – It refers to the number of columns in a relation
9. Primary Key – It refers to an attribute of a relation that uniquely identifies each
row/tuple in the relation/table.
10. Alternate Key – It also maintains uniqueness. A key which can become a primary key
but not chosen as a primary key is known as an alternate key.
11. Candidate Key – A candidate key is a candidate for becoming as a primary key. The
one which is selected is the primary key and the other is an alternate key.
12. Foreign Key – A field is known as a foreign key in one table which has the same field
as a primary key in the other table.
13. DDL – Data Definition Language.
14. DML – Data Manipulation Language.
15. TCL – Transaction Control Language.
16. Create Table & Create View – Create table creates the structure of the table and also
known as the base table whereas Create view creates a virtual table which is based on
the table or base table.
17. Drop Table & Drop View – Drop table deletes the table or the base table whereas Drop
view deletes the virtual table or the view. Once the table is deleted, the views on that
table will also be deleted automatically. If a view is deleted, then it does not affect the
base table from where the view is created.
18. Where and Having Clause – Where clause is used to put the conditions on individual
tuples/rows whereas Having clause is used to put conditions on groups and to put
condition with GROUP BY Clause, having is used.
19. Normalization – It is the process of transformation of the conceptual schema (logical
data structures) of the database into a computer representable form. The normalization
 process helps in attaining good database design thereby avoiding undesirable things
like repetition of information, inability to represent information, loss of information.
20. View – It is a virtual table which is derived from one or more tables.
21. DATA DICTIONARY – is a file that contains “metadata” i.e., “data about data”.