SQL : introduction

Introduction to SQL
(Standard Query Language)

Domain Types in SQL
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified
maximum length n.
 int. Integer (a finite subset of the integers that is machine-dependent).
 smallint. Small integer (a machine-dependent subset of the integer
domain type).
 numeric(p,d). Fixed point number, with user-specified precision of
p digits, with n digits to the right of decimal point.
 real, double precision. Floating point and double-precision floating
point numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at
least n digits.
 More are covered in Chapter 4.

Create Database Construct
 CREATE DATABASE dbname;
 Example:
CREATE DATABASE my_db;
Create Table Construct
 CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
....
);
 Example:
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2))

Integrity Constraints in Create Table
 not null
 primary key (A1, ..., An )
 foreign key (Am, ..., An ) references r
Example: Declare ID as the primary key for instructor
.
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (ID)
);
primary key declaration on an attribute automatically ensures not null

Integrity Constraints in Create Table
 CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderDetails varchar(255) NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(ID)
);

Insert into table
 INSERT INTO table_name
VALUES (value1,value2,value3,...);
 Example:
 insert into instructor values (‘10211’, ’Smith’, ’Biology’,
66000);
 insert into instructor values (‘10211’, null, ’Biology’, 66000);

Basic Query Structure
 A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
 Ai represents an attribute (desired column names)
 Ri represents a relation (table names)
 P is a predicate (Condition)

The select Clause
 Example: find the names of all instructors:
select name
from instructor
 NOTE: SQL names are case insensitive (i.e., you may use upper- or
lower-case letters.)
 E.g. Name ≡ NAME ≡ name
 Some people use upper case wherever we use bold font.

The select Clause (Cont.)
distinct
 Find the names of all departments with instructor, and remove
duplicates
select distinct dept_name
from instructor
all
 The keyword all specifies that duplicates not be removed.
select all dept_name
from instructor

The select Clause (Cont.)
 An asterisk in the select clause denotes “all attributes”
select *
from instructor
 The select clause can contain arithmetic expressions involving
the operation, +, –, , and /, and operating on constants or
attributes of tuples.
 The query:
select ID, name, salary/12
from instructor
would return a relation that is the same as the instructor relation,
except that the value of the attribute salary is divided by 12.

The where Clause
 To find all instructors in Comp. Sci. dept with salary > 80000
select name
from instructor
where dept_name = ‘Comp. Sci.' and salary > 80000
 Comparison results can be combined using the logical
connectives and, or, and not.
 Comparisons can be applied to results of arithmetic expressions
(<=, <, >, >=, !=).

Where Clause Predicates
between
 Example: Find the names of all instructors with salary between
$90,000 and $100,000 (that is,  $90,000 and  $100,000)
 select name
from instructor
where salary between 90000 and 100000
 select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

The from Clause
 The from clause lists the relations involved in the query
select 
from instructor, teaches
 generates every possible instructor – teaches pair, with all
attributes from both relations

Drop and Alter Table Constructs
 drop table customers
 Deletes the table and its contents
>>DROP TABLE table_name
>>DROP DATABASE database_name
 delete from customers
 Deletes all contents of table, but retains table
 DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND
ContactName='Maria Anders';

Drop and Alter Table Constructs
 alter table
 alter table- add
 ALTER TABLE table_name
ADD column_name datatype
 ALTER TABLE Customers
ADD Customer_Joindate Date
 alter table- drop
 ALTER TABLE table_name
DROP COLUMN column_name
 ALTER TABLE Customers
DROP Customer_Joindate Date

The Rename (As) Operation
 The SQL allows renaming relations and attributes using the as clause:
old-name as new-name
 E.g.
 select ID, name, salary/12 as monthly_salary
from instructor
 Find the names of all instructors who have a higher salary than
some instructor in ‘Comp. Sci’.
 select distinct T. name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’

Like- String Operations
 percent (%). The % character matches any substring.
 underscore (_). The _ character matches any character.
 Find the names of all instructors whose name includes the substring
“dar”.
select name
from instructor
where name like '%dar%'
 Match the string “100 %”
select name
from instructor
where attendance like ‘100 %' escape ''

Order by - Ordering the Display of
Tuples
 List in alphabetic order the names of all instructors
select distinct name
from instructor
order by name
 We may specify desc for descending order or asc for
ascending order, for each attribute; ascending order is the
default.
 Example: order by name desc
 Can sort on multiple attributes
 Example: order by dept_name, name

SQL : introduction

More Related Content

What's hot

Similar to SQL : introduction

More from Shakila Mahjabin

Recently uploaded

SQL : introduction