Data integrity, constraints

Data integrity, constraints
p60,61,67
FGAC : role based on User, Tables
Column-level Primary Key Constraints
CREATE TABLE table
(
Columname1 datatype(sizevalue) [CONSTRAINT constrainname] PRIMARY KEY,
Columname2datatype(sizevalue),
Columname3datatype(sizevalue)
)

Table-level Primary Key Constraints
CREATE TABLE table
(
Columname1 datatype(sizevalue),
Columname2datatype(sizevalue),
Columname3datatype(sizevalue)
[CONSTRAINT constrainname] PRIMARY KEY (Columname1)

)

Info

After 2:30 PM

Normalization
Different topic different table: No information overloading
Index: easy to find
Store procedure: package
Alias, synonyms: easy to call
View : several commands
Domain

create Table suppliers
(supno char(5) not null,
supname varchar (10),
supaddres varchar(40)
)

Update, Delete,

Update, Delete,

UPDATE customers
SET repid = 'E01'
WHERE repid is null

SELECT * FROM customers WHERE referredby is null

DELETE FROM customers WHERE referredby is null

Manipulating Table Data

Manipulating Table Data
P35 & P38: INSERT INTO

SELECT * FROM titles WHERE partnum = 39906

INSERT INTO titles (partnum, bktitle, devcost, slprice, pubdate)
VALUES('39906', 'VC++ HELLO WORLD', NULL, 50, '1999-06-01')

One table to another Table
INSERT INTO customers (custnum, custname, address, city,state,zipcode)
select custnum, custname, address, city,state,zipcode from potential_customers
where state ='ca'

1 PM

After 2 Query: Sub Query and Correlated Query
P 26: Filtering Grouped Data within a Subquery
Database Transformation service (DTS)
Suquery run before outer query : P28

vision of Scenario

^^^^^^^^^^^^^Scenario^^^^^^^^^^^^^^^^
The sales manager at a bookstore has been instructed by the top management to encourage high quantity purchasers to achieve the sales targets for the year. From the data on the sales made in the past, the manager has identified that the maximum quantity purchased by a customers who have bought 500 books in a single purchase. The manager also wants you to add the names of the books sold. The data required to generate the output is available in the customers, sales, and titles tables. Refer to the table structures in Appendix A for a description of the table columns.
^^^^^^^^^^^^^Topic D: ^^^^^^^^^^^^^^^^

SELECT partnum, bktitle, custname, address FROM titles, customers
WHERE 500 IN
(SELECT qty FROM sales
WHERE sales.partnum = titles.partnum
AND sales.custnum = customers.custnum)

Topic D: EXISTS

^^^^^^^^^^^^^Topic D: EXISTS^^^^^^^^^^^^^^^^
SELECT partnum, bktitle FROM titles
WHERE EXISTS
(SELECT * FROM sales
WHERE qty>200 AND sales.partnum = titles.partnum)
?????????????????????My curious?????????????????????
SELECT partnum, bktitle FROM titles
WHERE EXISTS
(SELECT * FROM sales
WHERE qty>200 AND sales.partnum = titles.partnum)

SELECT distinct bktitle, sales.qty FROM titles, sales
WHERE EXISTS
(SELECT * FROM sales
WHERE qty>200 AND sales.partnum = titles.partnum)
and sales.partnum = titles.partnum

After 11 AM

Distinct: 4
************************************************
select distinct (sales.repid), fname, lname FROM slspers, sales
WHERE qty > ANY
(SELECT SUM (qty) FROM sales GROUP BY repid)
AND sales.repid = slspers.repid

Without Distinct: 7
************************************************
select (sales.repid), fname, lname, qty FROM slspers, sales
WHERE qty > ANY
(SELECT SUM (qty) FROM sales GROUP BY repid)
AND sales.repid = slspers.repid

>>>>>>>>>> Lisa Ryan<<<<<<<<<

Lisa Prefer to understand
select distinct sales.repid, fname, lname FROM slspers, sales
WHERE sales.repid = slspers.repid AND
qty > ANY
(SELECT SUM (qty) FROM sales GROUP BY repid)

Sold Books

Have been sold: 55
select partnum, bktitle from titles
where partnum in ( select partnum from sales)
Haven’t been sold: 37
select partnum, bktitle from titles
where partnum not in ( select partnum from sales)

Join, subquery

/////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\
TITLES ------ SALES
1------------------ 2
2------------------ 1
3------------------ 5
4------------------ 3
: ------------------ :
1000-------------1

***********
55
select * from titles where partnum in ( select partnum from sales)
**********
98
select titles.partnum, bktitle, sldate from titles, sales
where titles.partnum = sales.partnum
*********

Outer
Left outer joined
Parent, Child
select customers.custname, sales.sldate from customers, sales
where customers.custnum = sales.custnum

select customers.custname, sales.sldate from customers join sales on
customers.custnum = sales.custnum

/////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\

&&&&&&&&&&&&&&&&&&
select customers.custname, sales.sldate from customers, sales
where customers.custnum = sales.custnum

select customers.custname, sales.sldate from customers join sales on
customers.custnum = sales.custnum

Before 10:30 AM

select * from sales
select * from slspers
select * from slsperf
select * from titles

select ordnum,qty, partnum, custnum from sales
where qty>(select avg(qty)from sales)

select partnum,devcost, bktitle, slprice, pubdate from titles
where slprice>(select avg(slprice)from titles)


select * from titles
where slprice>(select avg(slprice)from titles)

select * from customers
select custnum from customers
select * from customers where custnum in
(select custnum from sales)