Ch-8
Structure Query Language(SQL) is a database query language used for storing and
managing data in Relational DBMS. SQL was the first commercial language introduced
for E.F Codd's Relational model of database. Today almost all RDBMS(MySql, Oracle,
Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is
used to perform all types of data operations in RDBMS.
Advantages of SQL:-
1. High Speed: SQL Queries can be used to retrieve large amounts of records from a
database quickly and efficiently.
2. Well Defined Standards Exist: SQL databases use long-established standard, which
is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear
standard.
3. Easy to learn:- It is easy to learn and Using standard SQL it is easier to manage
database systems without having to write large amount of code.
Data types of Attribute in SQL
1. CHAR(n) :- This data type is used to store character or alphanumeric values in a
attribute. It has a fixed length. It will add spaces if characters not according the length.
We can give range between 0 to 255.
2. VARCHAR(n) :- This data type is used to store characters or alphanumeric values in a
attribute. It is a variable length data type. It stores actual allocated byte that is depend
upon the length of entered string.
3. INT :- INT specifies an integer value. Each INT value occupies 4 bytes of storage. The
range of values allowed in integer type are -2147483648 to 2147483647. For values
larger than that, we have to use BIGINT, which occupies 8 bytes.
4. FLOAT :- This data type is used to store floating or decimal points values in a
attribute. Each float value occupies 8 bytes.
5. DATE :- The DATE type is used for storing dates in 'YYYY-MM-DD' format in a
attribute. YYYY is the 4 digit year, MM is the 2 digit month and DD is the 2 digit date.
The supported range is '1000-01-01' to '9999-12-31'.
Data Constraints
Constraints are rules or conditions that are used on the table. It is used for maintain the
accuracy and integrity of the data inside table. Constraints are used to make sure that
the integrity of data is maintained in the database.
Following are the most used constraints that can be applied to a table.
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
1. NOT NULL :-
NOT NULL constraint restricts a column from having a NULL value. Once NOT
NULL constraint is applied to a column, you cannot pass a null value to that column. It
enforces a column to contain a proper value.
2. UNIQUE:-
UNIQUE constraint ensures that a field or column will only have unique values.
A UNIQUE constraint field will not have duplicate data.
3. PRIMARY KEY:-
Primary key constraint uniquely identifies each record in a database. A Primary Key
must contain unique value and it must not contain null value. Usually Primary Key is
used to index the data inside the table.
4. FOREIGN KEY:-
FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to
restrict actions that would destroy links between tables.
5. DEFAULT:-
DEFAULT constraint is used to set the default value for the column if no value is
provided.
RDBMS Languages
1. DDL(Data Definition Language) : DDL or Data Definition Language consists of the
SQL commands that can be used to define the database schema and it is used to
create and modify the structure of database objects in database.
Examples of DDL commands:
CREATE – It is used to create the database or its objects (like table, index,
function, views, store procedure and triggers).
1. Table:-
CREATE TABLE <table name>
( <field name> <data type> [constraint],
<field name> <data type> [constraint],
……
);
2. Database:-
CREATE DATABASE <database name>;
DROP – It is used to delete table or database. It will delete structure as well as
data also.
1. DROP TABLE <table name>;
2. DROP DATABASE <database name>;
ALTER- It is used to alter or modify the structure of the table or database.
1. Column add:-
ALTER TABLE <table name> ADD <column name> <data type>;
2. Column delete:-
ALTER TABLE <table name> DROP <column name>;
3. Change data type:-
ALTER TABLE <table name> MODIFY <column name> <data type>;
Examples:-
1. Add primary key to a relation:-
ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
2. Add constraint UNIQUE to an existing attribute :-
ALTER TABLE table_name ADD UNIQUE (attribute name);
3. Add an attribute to an existing table :-
ALTER TABLE table_name ADD attribute_name DATATYPE;
4. Modify datatype of an attribute :-
ALTER TABLE table_name MODIFY attribute DATATYPE;
5. Modify constraint of an attribute:-
ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;
6. Add default value to an attribute: -
ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;
7. Remove an attribute:-
ALTER TABLE table_name DROP attribute;
8. Remove primary key from the table:-
ALTER TABLE table_name DROP PRIMARY KEY;
DESCRIBE – It is used to see or view the structure of an already created table.
DESCRIBE <TABLE NAME>;
2. DML(Data Manipulation Language) : The SQL commands that deals with the
manipulation of data present in database belong to DML or Data Manipulation
Language and this includes most of the SQL statements.
Examples of DML:
▪ SELECT – is used to retrieve data from the a database.
▪ INSERT – is used to insert a record into a table.
▪ UPDATE – is used to update existing data within a table.
▪ DELETE – is used to delete records from a database table.
1. SELECT :- The SQL SELECT statement is used to fetch the data from a
database table which returns this data in the form of a result table.
SELECT <fields name>
FROM <tables name>
[WHERE <condition> ]
[ORDER BY ASC/DESC <field name>];
2. INSERT:- This is a DML command and it is used to insert a new record into a
table.
INSERT INTO <table name> VALUES (field1,field2,……..);
Example:-
INSERT INTO student VALUES (101, “Ramesh”, 85);
3. UPDATE:- This is a DML command and it is used to update existing data within
a table.
UPDATE <table name>
SET <filed name>=<value/expression>
[WHERE <conditon>] ;
Example-
UPDATE student SET marks=marks+5;
Note:- it will add 5 marks for all the students.
4. DELETE:- This is a DML command and it is used to delete record from a table.
DELETE FROM <table name>
[WHERE <condtion>];
Example:- DELETE FROM student WHERE rollno=105;
Note:- it will delete student record of rollno is 105.
SQL Keywords/Others Operators
1. DISTINCT:- It is used for eliminating all the duplicate records and fetching only
unique records from the table. It is used with SELECT command.
SELECT DISTINCT (sname) FROM student;
2. BETWEEN:- It will return the records where expression is within the range of value1
and value2.
SELECT sname FROM student WHERE marks BETWEEN 75 AND 90;
3. WHERE Clause:- It is used when we want to work on select records of a table.
SELECT * from student WHERE marks>90;
4. ORDER BY Clause:- It is used to display result of a query in ascending or
descending order. It is used with SELECT command. By default result will be display in
ascending order.
1. SELECT * from student ORDER BY marks DESC;
2. SELECT * from student ORDER BY sname ASC;
5. LIKE:- The LIKE operator is used in a WHERE clause to search for a specified pattern
in a column.
There are two wildcards used with LIKE operator:-
1. % :- zero, one or multiple character.
2. _ :- represent a single charcter.
Ex:- SELECT * FROM student WHERE sname LIKE ‘S%’;
SELECT * FROM student WHERE sname LIKE ‘%sh’;
SELECT * FROM student WHERE sname LIKE ‘_ _ _’;
6. NOT LIKE:- The NOT LIKE operator is used in a WHERE clause to search Unmatched
for a specified pattern in a column.
7. IS NULL:- It is used for checking whether a value in a field is NULL or not.
SELECT sname FROM student WHERE marks IS NULL;
