Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Customer - orders - salespeople database

Posted By: Molly Brown     Category: Oracle     Views: 14130

Article that create customer - orders - salespeople database with alter, insert, select, update, delete etc...

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
);

CREATE TABLE orders
(
  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;

Joining tables

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on Customer - orders - salespeople database Or get search suggestion and latest updates.

Molly Brown
Molly Brown author of Customer - orders - salespeople database is from London, United Kingdom.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!