Chapter 12: Structured Query Language
SQL is used to create and manage relational database in commercial world. The original version was developed at IBM. It provides a high level declarative interface for the users. When a user wants to get some information from a database file, he can issue a query. A query is a user–request to retrieve data or information with a certain condition. SQL is a query language that allows user to specify the conditions. (Instead of algorithms)
Processing Capabilities of SQL
DDL: It includes commands for defining / creating, deleting, modifying schema or structure of relation.
DML: It includes commands for inserting, deleting, selecting and modifying tuples in a relation.
SQL is used to embed with other programming languages like C, C++ etc...
ü View Definitionsà It includes commands for defining views.
ü Authorizationà Commands for specifying access rights for relation and views.
ü Integrityà Includes commands for specifying data integrity.
ü Transaction controlà Commands for beginning and ending of transactions.
Operations supported by SQL
· Schema definitions modification and delete, Insert, Modify, Delete and Retrieve data
· They are performed by DDL and DML Commands
· After the compilation of DDL commands create a file called Data Dictionary.
· After consultation of this modify the data in a database.
· By setting the queries we can retrieve data from the database.
Data types in SQLIn SQL data types are help us to indicating the types of values in a field of a table. They are classified in to 9. They are,
• Char
• Varchar
• Long varchar
• Smallint
• Int
• Dec
• Number
• Date
• Time
char
It is used for representing character type of data. Allocate memory storage of maximum number of characters in the field
Syntax:-
Attributename Char(size)
Example
Name char(25)
Place char(10)
Sex char
varchar
It allocates storage from 254 to 2048 characters for character type of data.
Syntax:-
Attributename varchar(size)
Example
remarks varchar(750)
Longvarchar
It allocates storage from 16K characters for character type of data.
Syntax:-
Attributename long varchar(size)
Example
prodinfo long varchar(2500)
Smallint
Used for representing small decimal numbers.
Syntax
Attributename smallint
Example
Slno smallint
Int
Used for representing decimal numbers without fraction.
Syntax
Attributename int
Example
age int
It automatically set the size.
Dec
Used for representing fractional decimal numbers.
Syntax
Attributename Dec (size, scale)
Example
percentage dec (5, 2)
It declares total 5 digits or more with 2 places for decimal point.
Number
Used for representing decimal numbers with fraction and correct size.
Syntax
Attributename number(size, scale)
Example
income number(5,2)
It declares with 5 digit number with 2 digit decimal point.
Date
Used for representing dates in a specified format.
Syntax
Attributename date
Example
dob date
Time
Used for representing time in a specified format.
Syntax
Attributename time
Example
tob time
SQL Commands
They are classified into two,
DDL Commands DML Commands
Create table Select
Alter Table Insert
Drop Table Update
Delete
View
Create table command
It is used to create a relation or table with a set of columns with constraints.
Syntax:
Create table Tablename
(columnname datatype (size) columnconstraint,
…………………………………,
………………………………… );
Rules for naming table
· Must not a SQL keyword, It should be unique.
· Must begin with an alphabet.
· Space, symbols, special symbols are not allowed, except underscore, characters and numbers.
· Uppercase and lowercase treated as same.
Column Constraints
They are some keywords by which some restrictions can be implementing in the desired columns. They are mention after the data type of the column. It ensures the integrity, consistency and redundancy. So it also called database integrity constraints. They are Not Null, Unique, Primary Key, Default and Check
Ø Not Null: - It ensures that a column can never have null or empty values.
Ø Unique: - It ensures that no two rows have the same value in the specified column.
Ø Primary Key: - It ensures a column contains a unique value and cannot contain a null value.
Ø Default: - To set a default value for column.
Ø Check: - To check or limit the values in a column.
Example1: Write query to create a table with the specifications given below.
Column name
|
Type of data and description
|
Admno
|
3 digit numeric value with primary key
|
Name
|
String of 20 characters
|
Sex
|
Single character with default value M
|
Course
|
String of 20 characters
|
Percent
|
A real number less than100
|
Query:
create table student
(admno number(3) primary key,
name char(20),
sex char default ‘M’,
course char(20),
percent number(5,2) check(percent<100));
Example2: Write query to create a table with the specifications given below.
Column name
|
Type of data and description
|
Empno
|
4 digit numeric value with primary key
|
Name
|
String of 25 characters
|
Age
|
2 Digit with default value 30
|
Salary
|
5 digits, not empty
|
Phone number
|
10 digit, Unique
|
Designation
|
15 characters, either ‘teacher or clerk’
|
Query:
Create table employee
(empno number(4) primary key,
Name char(25),
Age number(2) default ‘30’,
Salary number(5) not null,
Phone number(10) unique,
Designation char(15) check (designation in (‘Teacher’, ‘Clerk’)));
Alter table command
It is used to modify the structure of an existing table by adding columns or modifying columns. It has 2 forms ‘Alter table’ with ‘add’ clause, ‘Alter table’ with ‘modify’ clause.
‘alter table’ with ‘add’ clause
It is used to add new column in an existing table.
Syntax
Alter table tablename
Add (columnname datatype(size) constraints);
Example: Query to add a new column ‘grade’ in the ‘student’ table with constraint not null, the query as follows.
Query:
Alter table student
Add (grade char(2) not null);
‘alter’ table with ‘modify’ clause
It is used modify an existing column in an existing table.
Syntax:
Alter table tablename
Modify (coloumnname datatype(size) constraint);
Example:
Query to alter the column ‘rollno’ in ‘student’ table with size is 5 and primary key as follows.
Query
Alter table student
Modify (Rollno number(5) primary key);
Drop table command
It is used to delete an existing table.
Syntax:
drop table tablename;
Example: Query to delete ‘student’ table as follows.
Query:
drop table student;
DML Commands
They are Insert, Select, Update, Delete, View.
Insert Command
It is used to insert values or records in to a table. So it adds new row to a table. Here the clauses “into” and “values” are used.
Syntax:
Insert into tablename
Values(value1, value 2, …….);
Example: Query to add “rollno”, “name”, “score”, “garde” to the “student” table as follows.
Query:
Insert into student
Values(1, ‘Joseph’, 180, ‘A’);
Insertion with interaction
We can insert records in to a table in an interaction manner. For this the symbol ‘&’ is used, it is called substitution operator.
Syntax:
Insert into tablename values(&columnname1,&columnname2……);
Example: Query to insert records to the table ‘student’ in interaction fashion as follows.
Query:
Insert into student
values (&rollno, ‘&name’, &score, ‘&grade’);
It displays ‘Enter value for rollno on the screen, here we can enter appropriate data. It will continue at inserting grade.
Select Command
They help us to retrieve and display the records or information from a table according to a condition.
Syntax:
Select distinct/all columnnames
From tablename
Where condition
Order by columnname
Group by columnname
Having condition;
Usage of clauses
Distinct : To display the records without duplication.
All : To display all records with duplicates (*).
From : To indicate the table name.
Where : To specify the condition.
Order by : To group the record in a table.
Group by : To group a value in a column.
Having : To set condition for a selected group.
Examples for distinct, all, *, from clauses
· Write query to retrieve all records in the student table.
Query:
Select * from student;
· Write query to retrieve name and score in the student table.
Query:
Select name, score from student;
· Write query to retrieve all records in the student table with no duplicate marks.
Query:
Select distinct mark from student;
· Write query to retrieve name and grade in the student table.
Query:
Select name, grade from student;
Examples for ‘Where’ clause
In ‘where’ clause we can use the relational operators like =, >, <, >=, <=, <> and logical operators such as AND, OR, NOT also special operators such as IN, BETWEEN AND, LIKE, IS NULL can use.
· Write query to retrieve name and phone number of a student having age greater than 16 in student table.
Query:
Select name, phone
From student
Where (age>16);
· Write query to retrieve all students’ details with mark greater than 60.
Query:
Select *from student
Where (mark>60);
· Write query to retrieve name and mark of students with mark greater than 60 and age less than 19.
Query:
Select name, mark
from student
Where (mark>60 AND age<19);
· Write query to retrieve name, mark and age of students with age 16 or 17.
Query:
Select name, mark, age from student
Where (age=16 OR age=17);
· Write query to retrieve name and mark of students with mark other than 100.
Query:
Select name, mark
from student
Where (NOT mark=100);
· Write query to retrieve all details of students with name either Jayan or Joy.
Query:
Select *from student
Where (name IN(‘Jayan’, ‘Joy’));
· Write query to retrieve all details of students with marks between 30 and 60.
Query:
Select *from student
Where (mark BETWEEN 30 AND 60);
· Write query to retrieve all details of students with name begin with letter ‘J’.
Query:
Select *from student
Where (name LIKE ‘J%’);
· Write query to retrieve all details of students with name begin with any character but ends with the letter ‘oy’.
Query:
Select *from student
Where (name LIKE ‘_oy’);
· Write query to retrieve all details of students have no phone numbers.
Query:
Select *from student
Where (phone IS NULL);
Examples for ‘order by’ clause
It is used to retrieve data in ascending or descending manner from a table. For this the parameters like ASC and DESC is used. (ASC is default).
· Write query to retrieve all details of students in the order of their marks.
Query:
Select *from student
Order by mark desc;
· Write query to retrieve all details of students whose marks greater than 50 in the ascending order of name
Query:
Select *from student
Where (mark>50)
Order by name;
· Write query to retrieve class wise details of student in the order of name.
Query:
Select *from student
Order by class, name;
Examples for ‘group by’ clause
The group by clause is used to group the row in a table based on common values. The having clause is used to set a condition for a selected groups.
Consider the table given below
No
|
Name
|
Designation
|
1
|
Arun
|
Programmer
|
2
|
Deepa
|
Designer
|
3
|
Latha
|
Programmer
|
4
|
Raju
|
Analyst
|
5
|
Meena
|
Programmer
|
The following query produces a table given below.
Select designation, count(*)
From employee
Group by designation;
Designation
|
Count *
|
Programmer
|
3
|
Designer
|
1
|
Analyst
|
1
|
Examples for ‘group by’ and ‘having’ clause
Consider the table given below
No
|
Name
|
Designation
|
Salary
|
1
|
Arun
|
Programmer
|
25000
|
2
|
Deepa
|
Designer
|
20000
|
3
|
Latha
|
Programmer
|
25000
|
4
|
Raju
|
Analyst
|
15000
|
5
|
Meena
|
Programmer
|
25000
|
The following query produces a table given below.
Select designation, count(*)
From employee
Group by designation
Having (salary>20000);
Designation
|
Count *
|
Programmer
|
3
|
Select command with Aggregate functions
They are pre defied functions used to compute results of arithmetic operations and also used to select column for a table. They are,
Function
|
Usage
|
Example
|
AVG()
|
To return the average values of a column.
|
Select AVG (mark)
From student;
|
SUM()
|
To return the sum of values of a column.
|
Select SUM (price)
From book;
|
MAX()
|
To return the maximum value of a column.
|
Select MAX (mark)
From student;
|
MIN()
|
To return the minimum value of a column.
|
Select MIN (mark)
From student;
|
COUNT(*)
|
To return the number of records or rows in a table.
|
Select COUNT(*)
From student;
|
Update Command
It is used to change or modify or update the value in a column of a table. Here the clause ‘set’ is used. Also we can use the conditions by using ‘where’ clause.
Example1: Write query to change the ‘salary’ of all persons to 8000 in the table ‘employee’.
Query:
Update employee
Set salary=8000;
Example2: Write query to change the ‘salary’ of the person whose ‘idno’ =400 to 8000 in the table ‘employee’.
Query:
Update employee
Set salary=8000
Where(idno=400);
Example3: Write query to increment the ‘salary’ of all persons to 5% of current salary in the table ‘employee’.
Query:
Update employee
Set salary= salary * 0.05;
Example3: Write query to update the field ‘total’ in the ‘student’ table as sum of ‘sub1’, ‘sub2’ and ‘sub3’.
Query:
Update student
Set total=sub1+sub2+sub3;
Delete Command
It helps us to delete one or more records or row from a table according to a condition. Here the clauses like ‘from’ and ‘where’ are used.
Example1: Write query to delete all records from the table ‘student’.
Query:
Delete from student;
Example: Write query to delete records from the table ‘student’. Whose name is ‘Sarath’.
Query:
Delete from student
Where (name=‘Sarath’);
Nested Queries
The result of one query is used as the condition of another query is called nested query. Here the inner query is placed within the where clause of the outer query.
Example1: Write query to display the student name with highest mark in the student table.
Query:
Select name from student
Where (mark=select max (mark)
from student);
Example2: Write query to retrieve all details of students with minimum mark.
Query:
Select * from student
Where (mark= select min (mark)
From student);
View
A view is a temporary table used to provide different displays to an existing table.
Advantages
• It provides data security.
• Avoids data redundancy.
• Users get only wanted data.
Syntax
Create view view name
As select attribute names
from table name
from table name
Where condition
Example: Write a query to create a view from the table employee with name and salary with salary Rs.10000
Query:
Create view empview
As select name, salary
From employee
Where (salary=10000);
No comments:
Post a Comment