Introduction to SQL

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel

Content, examples and diagrams are taken from that book.

Chapter-I Introduction to SQL

Q:What is SQL and What does SQL do?

SQL stands for structured query language.

SQL is non-procedural language, therefore you specify what is to be done rather than how is it done.

American National Standards Institute (ANSI) prescribed a standard SQL.

SQL functions fits into two broad categories:

• It is a data definition language:(DDL):-SQL can create databse objects such as tables,indexes and views.SQL can also define access rights to these database objects.
• It is a data manipulation language(DML):-SQL can be used to insert,update,delete and retrieve data from the database

SQL is easy to learn

SQL can retrieve data from database

SQL can execute queries

SQL queries are used to answer question and also to perform actions such as adding,deleting table rows.

Q:Explain various datatypes available in SQL?

AnsThe following table shows some common SQL datatypes

Datatype	Format	Comments
Numeric	NUMBER(L,D)	Ex: NUMBER(7,2) indicates number will be stored with two decimal places and may be upto 7 digits long,including the sign and decimal places.
	INTEGER (OR) INT	Cannot be used if you want to store numbers that require decimal places.
	SMALLINT	Limited to integer values upto six digits
	DECIMAL(L,D)	Greater lengths are acceptable, but smaller ones are not.
Character	CHAR(L)	Fixed length character data for upto 255 characters. If you store strings that are not as long as the CHAR parameter value,the remaining spaces are left unused
	VARCHAR(L) OR VARCHAR2(L)	Variable length character data will not leave unused spaces.
Date	DATE	Stores dates in the julian date format.

Q: Explain how to create table using SQL?

Ans: The CREATE table is used to create a new table in the user database schema.

Syntax:

CREATE TABLE tablename (

Column1 datatype(column width) [constraints],

Column2 datatype(column width) [constraints],

……………

);

Example:

CREATE TABLE VENDOR(

    vno  number(3) PRIMARY KEY,

    vname varchar2(35) NOT NULL,

    vcity varchar2(15));

If the above command is executed successfully, the message “table created “ is displayed.

The following are the rules for naming a table.

1. Table names should start with an alphabet
2. Underscores,numbers and letters are allowed but not blank spaces.
3. Maximun length of table name is 30 characters.
4. Reserved words of ORACLE cannot be used as table name.
5. Two different table should not have the same name.
6. Unique column names should be specified.
7. Proper data types and size should be specified.

Q: What are SQL constraints? Explain?

Ans: Entity integrity is enforced automatically when the primary key is specified in CREATE TABLE command.

For Ex:

CREATE TABLE PRODUCT(

pno char(3),

pdesc varchar2(35) NOT NULL UNIQUE,

p_indate date,

qoh number(5),

price number(5),

vno number(3),

PRIMARY KEY(pno),

FOREIGN KEY(vno) REFERENCES VENDOR ON UPDATE CASCADE);

The primary key attribute contains both a NOT NULL and a UNIQUE specification.

The foreign key constraint definition ensures that

• You cannot delete a vendor from VENDOR table if atleast one PRODUCT row references that VENDOR.
• ON UPDATE CASCADE (not supported by ORACLE) ensures that when a change is made in VENDOR table, that change will be reflected automatically in PRODUCT table.

Besides the primary key and foreign key constraints, the ANSI SQL standard defines the following constraints.

• NOT NULL ensures that a column will not have null values.
• UNIQUE ensures that a column will not have duplicate values.
• DEFAULT defines a default value for a column(when no value is given).
• CHECK validates data in an attribute and sees that a specified condition exists.

Ex1: The minimum order must be atleast 10

Ex2:The date must be after APRIL 15, 2011

The CREATE TABLE command lets you define constraints in two different places.

• When you create the column definition (known as column constraint)
• When you use CONSTRAINT keyword (known as table constraint)

A column constraint applies to just one column.

A table constraint may apply to many columns.

Q:Explain important  data manipulation commands (DML) of SQL?

Ans:

INSERT: Used to enter data into a table.

Syntax:

INSERT INTO tablename VALUES (value1,value2,…..valuen)

Example:

INSERT INTO VENDOR VALUES (100,’RADHA’,’VJA’);

Observe that:

Character and date values must be entered between apostrophes(‘).

Numerical entries are not enclosed in apostrophes(‘).

Attribute entries are separated by commas.

Inserting Rows with NULL attribute

INSERT INTO product VALUES (‘P02’,’PENCIL’,’02-AUG-2011’, 25, 3, NULL);

Note that the NULL entry is accepted only because the vno attribute is optional in PRODUCT table.

The NOT NULL declaration is not used in the CREATE TAVLE statement for these attributes.

Inserting Rows with OPTIONAL attributes:

If the data is not available for all columns, then column list must be included following table name.

INSERT INTO product(pno,pdesc) VALUES(‘P03’,’MOUSE’)

   

COPYING PARTS OF A TABLE

To create a new table based on selected column and rows of an existing table. In this case, the new table will copy the attribute names,data characteristics and rows of original table.

CREATE TABLE part AS

SELECT pno,pdesc,vno FROM product;

Note that no entity integrity(primary key) or referential integrity (foreign key) rules are automatically applied to the new table.

Saving the table changes or COMMIT:

The COMMIT command permanently saves all changes such as rows added, attributes modified and rows deleted made to any table in the database.

Syntax:

COMMIT;

Any changes made to table contents are not saved on disk until you close the database, close the program you are using, or use the COMMIT command.

UPDATE Command:

The UPDATE command modifies an attribute value in one or more table rows.

Allows you to make data entries in an existing row’s columns.

Syntax:

UPDATE tablename

SET  columnname = expression [,columnname = expression]

WHERE conditionlist;

Ex:To change the p_indate of product with pno P01 to 02-AUG-2011.

UPDATE PRODUCT

SET p_indate =’02-AUG-2011’

WHERE pno=’P01’;

Restoring table contents or ROLLBACK:

ROLLBACK-undoes any changes since the last COMMIT command and brings the data back to the values that existed  before the changes were made.

Syntax: ROLLBACK;

Ex:  

1. Create table called sales.

2. Insert 10 rows in sales table.

3. Execute the ROLLBACK command

ROLLBACK will undo only the result of INSERT and UPDATE commands.

All data definition commands(CREATE TABLE) are automatically committed to data dictionary and cannot be rolled back.

DELETE Command

DELETE -deletes one or more rows from a table

If you do not specify a WHERE condition , all rows from table will be deleted.

REMOVAL OF SPECIFIED ROW(S):

Syntax:  DELETE FROM tablename [WHERE conditionlist];

REMOVAL OF ALL ROWS:

Syntax:  DELETE FROM tablename;

Viewing data in tables or SELECT

SELECT-lists the contents of a table.

Syntax:    

SELECT columnlist  

FROM tablename

[WHERE conditionlist];

The columnlist represents one or more attributes separated by commas.

You can use the * wildcard  character to list all attributes.

Ex1: SELECT * FROM PRODUCT;

Ex2: SELECT pdesc,p_indate FROM product WHERE pno=’P01’;

Ex3:SELECT * FROM product WHERE p_indate>’01-AUG-2011’;

The SELECT statement retrieves all rows that match the specified condition.

WHERE clause adds conditional restrictions to SELECT statement.

The condition list is represented by one or more conditional expressions separated by logical operators.

Comparison operators can be used to restrict output.

Comparison operators:

Symbol	Meaning	Example
=	Equal to	SELECT * FROM product WHERE pno=’P01’;
<	Less than	SELECT * FROM product WHERE price<10;
<=	Less than or equal to
>	Greater than	SELECT * FROM product WHERE price>10;
>=	Greater than or equal to
<> or !=	Not equal to	SELECT * FROM product WHERE vno <> 100;

Using Computed Columns

pno	qoh*price
P01	PEN
P02	PENCIL

Oracle uses actual formula text as the label for the computed column.

Ex: SELECT pno,qoh*price FROM PRODUCT;

Result:

Using Column aliases

pno	total
P01	PEN
P02	PENCIL

An alias is an alternative name given to a column or table in any SQL statement.

Ex2: SELECT pno,qoh*price AS total FROM PRODUCT;

Using Date arithmetic

SYSDATE is a special function that returns today’s date.

Ex:1   SELECT pno,p_indate,p_indate+90 AS ExpiryDate FROM product;

Ex:2    SELECT pno,p_indate,SYSDATE-90 AS  CutDate  FROM product

WHERE p_indate<=SYSDATE-90

The output would change based on today’s date

Arithmetic Operators:

Symbol	Meaning	Example
+	Addition
-	Subtraction
*	Multiply	SELECT qoh, price*qoh FROM product;
/	Division
^	Raised to power(some applications uses ** instead of ^)

Rules of Precedence: Perform operations within parentheses then perform ^ then *,/ then +,-

Logical Operators:

SQL allows you to have multiple conditions in a query through the use of logical operators.

Symbol	Meaning	Example
AND	Both conditions must match	SELECT * FROM product WHERE price > 10 AND price < 100;
OR	Either condition must match	SELECT * FROM product WHERE  vno = 100 OR vno = 101
NOT	Do not match a certain condition	SELECT  * FROM product WHERE NOT(vno = 100)

Display the result when all the condition specified using the AND operator are satisfied

Display the result when Either of the condition specified using the OR operator are satisfied

NOT operator is used to find rows that do not match a certain condition. It negates the result of conditional expression

Ex: SELECT * FROM product WHERE ( price < 50 AND p_indate > ’01-AUG-2011’) OR vno = 100;

The rows of vno=100 are included regardless of p_indate and price of those rows.

Special Operators

BETWEEN operator:

Used to check whether an attribute value is within a range

Ex: To see list of products whose price is between $10 and $100, use the command:

SELECT * FROM product WHERE price BETWEEN 10 AND 100;

IS NULL operator:

Used to check whether an attribute value is null.

Ex: To list all the products that do not have a vendor assigned, use the command:

SELECT * FROM product WHERE vno IS NULL;

LIKE operator:Used only with char and varchar2.

Matches a string pattern.

Used in conjuction with wildcards to find patterns within string attributes.

Ex1: To find all vendors whose name start with R

SELECT * FROM vendor WHERE vname LIKE ‘R%’;

To find all vendors whose name has ‘a’ as second letter.

Ex2: SELECT * FROM vendor WHERE vname LIKE ‘_a%’;

SQL allows you to use the percent sign (%) and underscore( _ ) wild card characters to make matches when the entire string is not known.

Wildcard	Meaning
%	Matches any characters
_	Matches one characters

Matches can be made when the query entry is written exactly like table entry.

IN operator: matches any value within a VALUE list.

uses an equality operator i.e, it selects only those rows that match(are equal to) atleast one of the values in the list

Ex:

SELECT * FROM product

WHERE  vno  IN(100 , 101);

All of the values in the list must be of same data type.

Each of the values in the value list is compared to the attribute.

IN operator is valuable when it is used in subqueries.

SELECT * FROM vendor

WHERE  vno IN(SELECT vno FROM product );

Subquery (SELECT vno FROM product) will list all vendors who supply products.

IN operator will compare the values generated by subquery to vno values in VENDOR table.

EXISTS operator:checks whether subquery returns any row.

If  subquery returns any row, run the main query otherwise don’t.

Ex:

SELECT * FROM vendor

WHERE  (SELECT * FROM product WHERE  qoh<=10);

Modifying structure of table:

ALTER Command: All changes to table structure are made using the ALTER command.

Syntax:

ALTER TABLE tablename

    {ADD|MODIFY} (columnname datatype [{ADD|MODIFY} columnname datatype]);

To Change column’s datatype

To change the vname datatype from varchar2 to char

ALTER TABLE vendor  MODIFY (vname char(35));

To Change column’s data characteristics

To increase the width of vname column to 55 characters

ALTER TABLE vendor MODIFY (vname char(35));

To add a column

ALTER TABLE product ADD (pmin number(5));

If the table already has some data , we cannot add  new column with NOT NULL as existing rows  will default to NULL for the new column.

TO ADD TABLE CONSTRAINTS:

Syntax: ALTER TABLE  tablename  ADD constraint [ADD constraint];

To add primary key:

ALTER TABLE part  ADD PRIMARY KEY(part_no);

To add foreign key:

ALTER TABLE part  ADD FOREIGN KEY(vno) REFERENCES vendor;

(OR)

ALTER TABLE part  ADD PRIMARY KEY(part_no)

                ADD FOREIGN KEY(vno) REFERENCES vendor;

To add primary and foreign key using the keyword CONSTRAINT:

ALTER TABLE part  ADD CONSTRAINT  pk_partno PRIMARY KEY(part_no)

        ADD CONSTRAINT fk_vno FOREIGN KEY(vno) REFERENCES vendor;

TO REMOVE A COLUMN OR TABLE CONSTRAINT

Synax:  ALTER TABLE tablename

DROP{ PRIMARY KEY | COLUMN  columnname | CONSTRAINT constraintname};

Dropping a column: deleting a column

ALTER TABLE product DROP COLUMN pmin;

DELETING A TABLE FROM DATABASE:

A table can be deleted from the database using the DROP TABLE command.

Syntax:

DROP TABLE part;

Advanced select queries

ORDER BY clause: Orders the selected rows based on one or more attributes

• Used in the last portion of select statement
• By using this, rows can be sorted
• By default it takes ascending order
• DESC is used for sorting in descending order
• Sorting by column which is not in select list is possible.
• Sorting by column aliases

Example: To produce a list of products sorted in descending order of their prices.

SELECT pno,pdesc,p_indate,price

FROM product

ORDER BY price DESC;

A multilevel ordered sequence is known as cascading order sequence and it can be created easily by listing several attributes, separated by commas, after the ORDER BY clause.

SELECT * FROM employee ORDER BY e_lname,e_fname,e_initial;

DISTINCT clause: Used to eliminate duplicate rows.

Ex:How many different vendors are currently represented in the PRODUCT table?

SELECT DISTINCT vno FROM product;

Explain Aggregate functions?

Ans: Some of the aggregate functions are COUNT,MIN,MAX,AVG.

COUNT:   Uses one parameter within parantheses.

COUNT(columnname)-Used to count the number of non-null values of an attribute

COUNT(*) aggregate function is used to count number of rows returned by query, including the rows that contain nulls.

Ex1: How many rows in PRODUCT table have a price value less than or equal to $500?

SELECT COUNT(*) FROM product WHERE price<=500;

Ex:2:How many vendors referenced in the PRODUCT table have supplied products with prices that are <+1?

SELECT COUNT(DISTINCT vno) FROM product WHERE price<=10;

MAX and MIN

Ex1: Which product has highest price?

SELECT * FROM product WHERE price = (SELECT MAX(price) FROM product);

(Here we cannot use SELECT * FROM product WHERE price = MAX(price); because The aggregate functions can be used only in column list of a SELECT statement)

Ex2:Highest price in PRODUCT table?

SELECT MAX(price) FROM product;

Ex:3Lowest price in PRODUCT table?

SELECT MIN(price) FROM product;

Ex4: To find out the product that has the oldest date?

SELECT * FROM product WHERE price = (SELECT MIN(p_indate) FROM product);

Ex5: To find out the most recent product.

SELECT * FROM product WHERE price = (SELECT MAX(p_indate) FROM product);

SUM: Computes total sum of any specified attribute.

Ex:To find the total value of all items

SELECT SUM(qoh*price) AS TOTALVALUE

FROM product;

AVG

Ex1: To find the products whose prices exceed the average product price.

SELECT * FROM product

WHERE price > (SELECT AVG(price) FROM product)

ORDER BY price desc;

Explain about GROUP BY clause?

• Used to group rows on basis of certain common attribute value such as employees of a department, products of a vendor.
• WHERE clause can be used ,if needed.
• The only attributes that can be put in select clause are the aggregated functions and the attributes that have been used for grouping the information.

Ex1:How many products are supplied by each vendor?

SELECT vno, COUNT(pno)

FROM product

GROUP BY vno;

Having clause:

Extension of the GROUP BY feature is the HAVING clause.

HAVING clause is applied to the output of GROUP BY operation.

Ex: how many products supplied by each vendor.List only the products whose average is below $10

SELECT vno, COUNT(pno), AVG(price)

FROM product

GROUP BY vno

HAVING AVG(price) < 10;

Q: Explain about index in SQL

Ans:

Indexes are used to quickly access the data.

Syntax: CREATE INDEX <index name> ON <tablename>(column name);

An index can be created on one or more columns.

Based on the number of columns included in index, an index can be of 2 types.

1. Simple index    2.Composite Index.

To create Simple index:

An index created on a single column is called simple index.

Ex: CREATE INDEX p_in ON product(p_indate)

To create composite index:

An index created on a more than one  column is called composite index.

Dropping indexes or deleting an index: Use the DROP INDEX command.

Ex: DROP INDEX  p_in;

Q:What is database schema?

Ans:A schema is a group of database objects such as tables and indexes, that are related to each other.  Syntax: CREATE SCHEMA AUTHORIZATION {creator}

When a user is created, the DBMS automatically assigns schema to that user.

Schemas are useful to group tables by owner and enforce a first level of security by allowing each user to see only the tables that belong to that user.

Labwork:

VENDOR
vno	vname	vcity
100	RADHA	VJA
101	ALIYA	NULL
102	SIRI	VJA
103	LAK	GNT

CREATE TABLE VENDOR(

    vno  number(3) PRIMARY KEY,

    vname varchar2(35) NOT NULL,

    vcity varchar2(15));

PRODUCT
pno	pdesc	p_indate	qoh	price	vno
P01	PEN	01-AUG-2011	20	10	100
P02	CD	06-AUG-2011	2000	12	103
P03	PENCIL	07-AUG-2011	25	3	NULL
P04	DVD	08-AUG-2011	100	350	101
P05	CELL	09-AUG-2011	5	5000	100
P06	MOUSE	-	-	-	-

CREATE TABLE PRODUCT(

pno char(3),

pdesc varchar2(35) NOT NULL UNIQUE,

p_indate date,

qoh number(5),

price number(5),

vno number(3),

PRIMARY KEY(pno),

FOREIGN KEY(vno) REFERENCES VENDOR);

CUSTOMER
cno	cname	city	baldue
201	ANU	VJA	100
202	ASHA	GNT	500
203	RAJ	VJA

CREATE TABLE CUSTOMER(

cno number(3) PRIMARY KEY,

cname varchar2(35) ,

city varchar2(5),

baldue number(5));

INVOICE
invno	cno	invdate
301	201	20-AUG-2011
302	202	20-AUG-2011
303	203	21-AUG-2011
304	201	21-AUG-2011

CREATE TABLE INVOICE(

invno number(3),

cno number(3),

invdate date,

PRIMARY KEY(invno),

FOREIGN KEY(cno)  REFERENCES CUSTOMER);

All products sold are stored in LINE table

CREATE TABLE LINE(

LINE
invno	lineno	pno	line_units	line_price
301	L01	P01	10	10
301	L02	P02	10	12
301	L03	P03	20	3
302	L01	P01	30	10
302	L02	P02	20	12
303	L01	P01	35	10
303	L02	P02	15	12

invno number(3),

lineno    char(3),

pno char(3),

line_units number(5),

line_price number(5),

PRIMARY KEY(invno,lineno),

FOREIGN KEY(pno)  REFERENCES PRODUCT

FOREIGN KEY(invno)  REFERENCES INVOICE);

EMP
e_lname	e_fname	e_initial	dob	Sal
REDDY	SAM	A	14-NOV-1994	15000.25
NAIDU	ANU	S	14-OCT-1992	16234.50
JAIN	NEHA	K	28-NOV-1993	15623.48
REDDY	RAM	T	14-SEP-1994	1623.89

CREATE TABLE EMP(

e_lname varchar2(20),

e_fname varchar2(20),

e_initial varchar2(2),

dob date,

sal  number(8,2));