Create table statements
CREATE TABLE Salespeople
(
snum integer PRIMARY KEY,
sname CHAR(10) NOT NULL,
city CHAR(10),
comm NUMBER(10,2)
);
CREATE TABLE Customers
(
cnum integer PRIMARY KEY,
cname char(10) NOT NULL,
city CHAR(10),
rating INTEGER,
snum integer REFERENCES Salespeople
);
(
onum integer PRIMARY KEY,
amt DECIMAL,
odate DATE NOT NULL,
cnum INTEGER NOT NULL,
snum INTEGER NOT NULL,
FOREIGN KEY (snum) REFERENCES Salespeople,
FOREIGN KEY (cnum) REFERENCES Customers
);
Drop table by drop statements
DROP TABLE Salespeople;
DROP TABLE Customer;
DROP TABLE orders;
Insert records by insert statements
INSERT INTO Salespeople VALUES (1,'jayesh','ahmd',.20);
INSERT INTO Salespeople VALUES (2,'mukesh','ahmd',.10);
INSERT INTO Customers VALUES (1,'sanjay','ahmd',1,1);
INSERT INTO Customers (cnum,cname,city,snum) VALUES (2,'paresh','ahmd',1);
INSERT INTO orders VALUES (1,1000,'10/JAN/2000',1,1);
INSERT INTO orders VALUES (2,1000,'10/JUN/2002',1,1);
Add constraint using alter statement
ALTER TABLE Customers ADD CONSTRAINT fk_customers FOREIGN KEY (snum) REFERENCES Salespeople;
ADDING CONSTRAINTS
i) Data type : integer
ii) Size : char(10)
2 types :
i) Column constraint : applies to single col
ii)Table constraint : applies to multiple col
PRIMARY KEY :
i) Single col P.K. :
ii) Multiple col P.K :
iii) CHECK constraint : define conditions (predicate)
Condition : commission of .15 and above is only allowed for 'ahmd' salesman.
CREATE TABLE Salespeople
(
snum integer PRIMARY KEY
fname char(10),
lname char(10),
city char(20) CHECK (city IN ('ahmd','mumbai','delhi'))
comm NUMBER(10,2) CHECK (comm < 1),
CHECK (comm < 0.15 OR city='ahmd')
);
comm = .20 ( false OR true ) = true
comm = .10 and city='mumbai' = (true OR false) = true
comm = .20 and city='mumbai' = (false OR false) = false
comm = .10 and city='ahmd' = (true OR true) = true
ASSIGNED DEFAULT VALUES :
CREATE TABLE Salespeople
(
snum integer PRIMARY KEY
fname char(10),
lname char(10),
city char(20) DEFAULT='ahmd',
comm NUMBER(10,2)
);
MAINTAINING THE INTEGRITY OF DATA
# F.K. :
Referential Integrity :
# F.K. as table constraint :
CREATE TABLE orders
(
onum INTEGER PRIMARY KEY,
amt decimal,
cnum INTEGER NOT NULL,
snum INTEGER NOT NULL,
FOREIGN KEY(cnum,snum) REFERENCES Customers (cnum,snum));
)
CONSTRAINS ON UPDATION :
3 possibilites :
i) you can RESTRICT The Change In Parent Key
ii) CASCADE OPTION
iii) #F.K. as NULL
CREATE TABLE Customers
(
cnum integer,
cname char(10),
snum integer REFERENCES Salespeople(snum),
PRIMARY KEY (cnum,snum)
);
CREATE TABLE Orders
(
onum integer PRIMARY KEY,
amt decimal,
cnum integer NOT NULL REFERENCES Customers
snum integer REFERENCES Salespeople,
UPDATE OF Customers CASCADES,
DELETE OF Customers RESTRICTED,
UPDATE OF Salespeople CASCADES,
DELETE OF Salespeople NULLS );
#F.K. can refer to their own tables.
CREATE TABLE employee
(
empno INTEGER NOT NULL UNIQUE,
name char(10),
managerno integer REFERENCES employee(empno)
);
Entering values into TABLES - the INSERT statement
INSERT INTO tablename VALUES (value,...);
INSERT INTO orders VALUES (10,1500,'1-JAN-2000',1,1001);
Entering values into TABLES - INSERT STATEMENT
INSERT INTO tablename VALUES (value1,value2,...);
INSERTING NULLS
INSERT INTO Salespeople
VALUES (1,'sanjay',NULL,5,NULL);
INSERTING THE RESULTS OF A QUERY
you can use INSERT command to take values from one table and place then in another by using Query.
INSERT INTO Ahmd_Salespeople
SELECT * FROM Salespeople
WHERE city='ahmd';
Removing rows from TABLES - DELETE statement
DELETE FROM Salespeople WHERE snum=101;
CHANGING FIELD VALUES - UPDATE Statement
UPDATE tablename
SET fieldname = newvalue [WHERE condition];
UPDATE Customers
SET rating=10 WHERE city='ahmd';
UPDATE Customers
SET rating=10,city='ahmd' WHERE snum=1002;
Using VALUE Expressions in UPDATE
UPDATE Salespeople SET comm = comm * 2;
DISTINCT of SELECT statement argument allows to eliminate duplicate values.
SELECT DISTINCT snum FROM orders;
USING INEQUALITIES in Predicates of SELECT statement.
following relational operator can be used in SQL :
a) = equal to
b) >,<,<=,>=
c)<> not equal to
SELECT * FROM Customers WHERE rating >= 2;
SELECT * FROM Customers WHERE rating <> 2;
WORKING WITH NULL values
- NULL means missing value or non-existant value.
- NULL does not have a datatype.
USING Boolean Operators in Predicates(conditions)
- Boolean operators in SQl are : AND,OR,NOT.
- In SQL the output of a Boolean expression can be : TRUE,FALSE and UNKNOWN.
- When NULL participates in a Boolean expression, output is NULL.
e.g. TRUE OR NULL = unknown.
FALSE AND NULL = unknown.
SELECT * FROM Customers WHERE RATING > 1;
- in output ,if rating field of a row is NULL,then that record will not be included in output.
Complex boolean expression :
SELECT * FROM orders
WHERE NOT ((odate='10/JAN/2000' AND snum < 10) OR amt > 100);
IN OPERATOR
- defines a set in which a given value may or may not be included.
e.g. to find salespeople who are located in either ahmd or bombay
SELECT * FROM salespeople
WHERE city='ahmd' OR city='bombay' OR ;
SELECT * FROM salespeople
WHERE city IN ('ahmd','bombay');
SELECT * FROM customers
WHERE cnum IN (1,2,10,11,12);
BETWEEN operator
- defines a range that values must fall into to make the predicate TRUE.
- Start value and End value should be in increasing order.
SELECT * FROM Salespeople
WHERE comm BETWEEN .10 and .12;
- .10 and .12 is inclusive in output.
SELECT * FROM Salespeople
WHERE comm BETWEEN .10 and .12
AND NOT comm IN (.10,.12);
LIKE OPERATOR
- it searches a text column to see if part of ti matches a string.
- you can use wildcards,special chars that will match anything.
- _ stands for any single char.
- % stands for sequence of any number of chars.
- you can define a single char as escape character - to escape keyworks % and _ in search
E.g.
find the names of customers whose name start with letter 'a';
SELECT cname FROM Customers WHERE cname LIKE 'a%';
find the names of customers having 'a' as any letter in their names;
SELECT cname FROM Customers WHERE cname LIKE '%s%';
SELECT * FROM Salespeople WHERE sname LIKE 's____y%';
Escape chars
SELECT * FROM Salespeople WHERE sname LIKE '%/_%' ESCAPE '/';
IS NULL operator
- when null is compared with any value ,Result is known
- SQL provides special operator IS NULL to locate and treat NULL values.
SELECT * FROM Customers WHERE city IS NULL;
SUMMARIZING DATA with AGGREGATE FUNCTIONS
- COUNT : no. of rows or non-null column values that query selected.
SELECT COUNT(city) FROM Customers;
- SUM :
SELECT SUM(amount) FROM orders;
- AVG :
SELECT AVG(amount) FROM orders;
- using DISTINCT WITH COUNT : count of only different values in a given column
SELECT COUNT(DISTINCT city) FROM Customers;
- Counting rows
SELECT COUNT(*) FROM orders;
- USING ALL with COUNT : reverse of DISINTCT i.e. to include DUPLICATES
SELECT COUNT(ALL city) FROM Customers;
AGGREGATES BUILT-ON expressions
- you can also use aggregate functions with arguments that consists of expressions involving 1 or more columns
Find average commission and express it as percentage
SELECT AVG(comm*100) FROM Salespeople;
To find the largest order taken by each Salesperson.
- you group the query by snum values and then calculate the MAX separately for each such group.
SELECT snum,MAX(amt) FROM orders GROUP BY snum;
- you can also use GROUP BY with multiple columns.
Find the largest order taken by each salesperson on each date.
SELECT snum,odate,MAX(amt) FROM orders GROUP BY snum, odate;
HAVING CLAUSE
INSERT INTO Salespeople VALUES (2,'paresh',NULL,5,NULL);
INSERT INTO orders VALUES (3,1500,'1-JAN-2000',1,2);
Find the largest order taken by each salesperson on each date.
SELECT snum, odate,MAX(amount) FROM orders GROUP BY snum,odate;
Find the largest order taken by each salesperson on each date WHERE MAXIMUM AMOUNT SHOULD BE GREATER THAN 500.
SELECT snum,odate,MAX(amount) FROM orders WHERE MAX(amount) > 500 GROUP BY snum, odate;
- you cannot use aggreate functions in WHERE clause.
- when you want to apply conditions based on aggregate fields,USE HAVING CLAUSE.
SELECT snum,odate,MAX(amount) FROM orders GROUP BY snum, odate
HAVING MAX(amount) > 500;
- YOU CAN ALSO apply conditions on the columns chosen by GROUP BY.
SELECT snum,MAX(amount) FROM orders GROUP BY snum HAVING snum IN (1,7);
VALUE EXPRESSIONS in the SELECT CLAUSE
- SQL allows you to place scalar expressions and constants among the selected columns.
SELECT snum,sname,city,comm*100 FROM Salespeople;
NAMING EXPRESSION COLUMNS IN OUTPUT
SELECT snum,sname,city,comm* 100 AS PERCENT FROM Salespeople;
PUTTING TEXT IN YOUR QUERY OUTPUT
- you can insert constants in the SELECT clause of a query.
SELECT snum, sname, city, comm*100 AS percent, '%' FROM Salespeople;
- you can label output with inserted comments.
SELECT 'FOR',odate, ',there are', COUNT(DISTINCT onum), 'orders.' FROM orders GROUP BY odate;
ORDERING OUTPUT BY COLUMN VALUES
- you can order the query output according to the values in 1 or more selected columns.
- you can orders multiple columns one inside another.
- you can specify ascending(ASC) OR descending (DESC) for each column.
SELECT * FROM orders ORDER BY cnum DESC;
INSERT INTO orders VALUES (4,2500,'1-MAY-2000',2,2);
INSERT INTO orders VALUES (5,5500,'11-MAY-2000',2,2);
ORDERING BY MULTIPLE COLUMNS
SELECT * FROM Orders ORDER BY cnum DESC,amount DESC;
USING ORDER BY WITH GROUP BY
- ORDER BY always follows the GROUP BY and HAVING clauses.
SELECT snum,odate,MAX(amount) FROM orders GROUP BY snum,odate ORDER BY snum;
ORDERING OUTPUT BY COLUMN NUMERS
- in place of column names you can use numbers to indicate the columns being used to order the output
SELECT sname,comm FROM salespeople ORDER BY 2 DESC;
- In order to order the columns resulting from Aggregate functions,expressions, use number to refer to them.
Find the number of orders taken by each salesman in descending order of their number.
SELECT snum,COUNT(onum) FROM orders GROUP BY snum ORDER BY 2 DESC;