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)