Intro To SQL

Intro to SQL
Structured Query Language

• Language that provides an interface to relational database systems.
• Developed by IBM in 1970s for use in System R and is de facto

standard as well as an ISO and ANSI standard.
Many standards out there:

• ANSI SQL, SQL92 (a.k.a. SQL2), SQL99 (a.k.a. SQL3), ….
• SEQUEL: Structured English QUEry Language;

Components of SQL
• Data Definition Language (DDL)
• Used to create, modify and delete database structures but not data.
• Not used by general user and are limited to DBA.
• Immediate statements i.e. not susceptible to roll back.
DDL statements include:
CREATE: To create objects in database

ALTER: Alters the structure of database
DROP: Delete objects from database
COMMENT: Add comments to the data dictionary
GRANT: Gives user’s access privileges
REVOKE: Withdraw access privileges given with the GRANT command
• Data Manipulation Language (DML)
• Allows changing data within the database.
• Query one or more tables
DML statements include:
INSERT: To insert data into a table

UPDATE: Updates existing data within a table
DELETE: Delete all records from a table
LOCK: Table Control Concurrency
• Data Control Language (DCL)
• Control access to data and to the database
• Occasionally grouped with DML statements
• COMMIT: Save Work Done

• SAVEPOINT: Identify a point in a transaction to which we can roll back
• ROLLBACK: Restore database to original since last COMMIT.
• GRANT/REVOKE
• Data Query Language (DQL)

• SELECT: Retrieve Data from the database
Basic Data Types
• character-string
• CHAR(N) (or CHARACTER(N)) is a fixed-length character string. Maximum characters
supported 255
• VARCHAR(N) (or CHAR VARYING(N), or CHARACTER VARYING(N)) is a variable-length
character string with at most N characters.
• number
• Both fixed and floating point
Constraints in SQL
• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies

each row in a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Use to create and retrieve data from the database very quickly
Create TABLE
• A name can have maximum upto 30 characters

• Allowed charset: A-Z, a-z and numbers 0-9
• Name should begin with an alphabet
• _ is allowed
• No Reserve words
Syntax
Create Table <tablename> (<column name 1><Datatype> (<size>),

<Columnname2><Datatype> (<size>));
CREATE TABLE student_master (
stuID number(3) NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
);
SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

stuID number(3) NOT NULL UNIQUE,
FirstName varchar(255) NOT NULL,
);
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.

stuID number(3) PRIMARY KEY,
FirstName varchar(255),
);
Alter TABLE
To add or drop columns on existing tables.
ALTER TABLE statement syntax:

ALTER TABLE <table name>
ADD attr datatype (size);
ALTER TABLE FoodCart (
or ADD sold int
DROP COLUMN attr; );
ALTER TABLE FoodCart(

DROP COLUMN profit
);
To create a PRIMARY KEY constraint on the "ID" column when
the table is already created
ALTER TABLE Persons

ADD PRIMARY KEY (ID);
ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
ALTER TABLE Persons

DROP PRIMARY KEY;
ALTER TABLE Persons

DROP CONSTRAINT PK_Person;
CREATE TABLE Persons (
ID number(3) NOT NULL,
Age number(2) CHECK (Age>=18)
);
ALTER TABLE Persons

ADD CHECK (Age>=18);
stuID number NOT NULL,
Age number(2),
City varchar(255) DEFAULT ‘ABC'
);
FOREIGN KEY
CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Insert Statement
To insert a row into a table, it is necessary to have a value for each attribute,
and order matters.
INSERT statement syntax:

INSERT into <table name>
VALUES ('value1', 'value2', NULL);
Example: INSERT into FoodCart
VALUES (’02/26/08', ‘pizza', 70 );
Update Statement
To update the content of the table:

UPDATE statement syntax:
UPDATE <table name> SET <attr> = <value>
WHERE <selection condition>;
Example: UPDATE FoodCart SET sold = 349
WHERE date = ’02/25/08’ AND food = ‘pizza’;
Select Statement
• The main element in a SQL query is the SELECT statement.

• A properly written SELECT statement will always produce a result in
the form of one or more rows of output.
• The SELECT statement chooses (selects) rows from one or more tables
according to specific criteria.
Example
SELECT *
FROM employee;
•The asterisk (*) tells Oracle to select (display) all columns contained in the
table “employee”.
THE DISTINCT CLAUSE
• Oracle provides a means for eliminating duplicate rows in a result table
through use of the DISTINCT keyword .
SELECT emp_salary
FROM employee;
EMP_SALARY
-----------------
$55,000.00
$43,000.00
$43,000.00
$25,000.00
$25,000.00
$30,000.00
$38,000.00
$25,000.00
8 rows selected.
• The query is rewritten using the DISTINCT keyword to eliminate
duplicate rows.
SELECT DISTINCT emp_salary
FROM employee;
EMP_SALARY
-------------------
$25,000.00
$30,000.00
$38,000.00
$43,000.00
$55,000.00
THE WHERE CLAUSE
• Specific rows can be selected by adding a WHERE clause to the SELECT

query.
SELECT eid, ename, dept
FROM employee
WHERE emp_salary >= 35000;
Operator
= equal to
< less than
> greater than
>= greater than or equal to
<= less than or equal to
!= not equal to
<> not equal to
!> not greater than
!< not less than
Comparing Character Data
• Comparison operators are not limited to numeric data.

• They can also be used with columns containing character data.
• If the value is a character string or date, you must surround the value
(string of characters) with which a column is being compared with single
quotation (' ') marks.
SELECT emp_id, emp_name FROM employee

WHERE emp_gender = 'M';
THE ORDER BY CLAUSE
• Output from a SELECT statement can be sorted by using the optional ORDER BY
clause.
SELECT emp_last_name, emp_first_name
FROM employee
WHERE emp_last_name >= 'J'
ORDER BY emp_last_name;
EMP_LAST_NAME EMP_FIRST_NAME
------------------------- --------------------------
Joshi Dinesh
Joyner Suzanne
Markis Marcia
To sort columns from high to low, or descending, an optional keyword DESC
must be specified.
ASC - Ascending, low to high.

DESC - Descending, high to low
IN and NOT IN
Select * from table where field IN (value1, value2)
Select FNAME, LNAME from emp where FNAME IN (‘Amit’, ‘Mohit’)
Aggregate Functions
AVG, MIN, MAX, COUNT, SUM
Select AVG(sal) from emp;
Select AVG(sal) “Employee Salary” from emp;

• COUNT: Returns the number of rows.
• COUNT(*) Returns the number of rows including duplicates and nulls.
• Select count(eid) from emp;
• MAX: Returns max value of expression.

• Select max(sal) from emp;
EXERCISE
Create the Table Column Data Type Size Default Attributes
Name
Name: Client_Master
Clientno Varchar2 6 PK
CName Varchar2 20 NOT NULL
Address1 Varchar2 30
• Create table with all the attributes and
constraints. City Varchar2 15
• Find out the names of all the clients Pincode Number 8 Should Start
• Retrieve the entire contents of Client_Master with A
table State Varchar2 15
• Retrieve the list of names, city and the state of Baldue Number 10,2
all the clients.
• Change the city of any one client.
• Change the baldue of any client to Rs 5000
• Delete the record of client where client no=101
• Find the client whose Baldue is maximum.
• Find the names of clients with second highest
baldue.
• Find all those clients whose name starts with ‘A’

Intro To SQL

Uploaded by

Copyright:

Available Formats

Intro To SQL

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Intro To SQL

Uploaded by

Copyright:

Available Formats

Intro to SQL

Structured Query Language

• Developed by IBM in 1970s for use in System R and is de facto

Many standards out there:

• SEQUEL: Structured English QUEry Language;

DDL statements include:

CREATE: To create objects in database

DML statements include:

INSERT: To insert data into a table

• COMMIT: Save Work Done

• Data Query Language (DQL)

• UNIQUE - Ensures that all values in a column are different

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies

• FOREIGN KEY - Uniquely identifies a row/record in another table

• CHECK - Ensures that all values in a column satisfies a specific condition

• DEFAULT - Sets a default value for a column when no value is specified

• A name can have maximum upto 30 characters

Create Table <tablename> (<column name 1><Datatype> (<size>),

SQL UNIQUE Constraint

CREATE TABLE student_master (

CREATE TABLE student_master (

ALTER TABLE statement syntax:

ALTER TABLE FoodCart(

ALTER TABLE Persons

ALTER TABLE Persons

ALTER TABLE Persons

ALTER TABLE Persons

ALTER TABLE Persons

CREATE TABLE Orders (

ALTER TABLE Orders

INSERT statement syntax:

To update the content of the table:

• The main element in a SQL query is the SELECT statement.

• Specific rows can be selected by adding a WHERE clause to the SELECT

• Comparison operators are not limited to numeric data.

SELECT emp_id, emp_name FROM employee

ASC - Ascending, low to high.

Select * from table where field IN (value1, value2)

Select FNAME, LNAME from emp where FNAME IN (‘Amit’, ‘Mohit’)

AVG, MIN, MAX, COUNT, SUM

Select AVG(sal) from emp;

Select AVG(sal) “Employee Salary” from emp;

• Select count(eid) from emp;

• MAX: Returns max value of expression.

You might also like