SQL Operations and Constraints

This assignment is a database assignment that contains various SQL queries, table creation and manipulation, data insertion, deletion, and updating. The assignment consists of four parts: Part 1 contains SQL queries to create tables, insert data, delete rows based on certain conditions. Part 2 contains SQL queries to update data in the tables. Part 3 explains the use of UPDATE statements with various conditions. Part 4 demonstrates the operations that will be rejected by the system due to foreign key constraints.

0 | P a g e
0
database assignment
STUDENT NAME:
8 November 2016

Contents
PART 1...................................................................................................................................................2
PART 2...................................................................................................................................................3
PART 3...................................................................................................................................................5
PART 4...................................................................................................................................................5

PART 1
a. SELECT * FROM CUSTOMER;
b. SELECT CUSTI_ID, CUST_TITLE FROM CUSTOMER;
c. SELECT * FROM LEAD WHERE AMOUNT > 12000;
d. SELECT * FROM LEAD WHERE AMOUNT <= 12000 AND POSSIBILITY > 50;
e. SELECT * FROM LEAD WHERE AMOUNT >= 12000 OR POSSIBILITY > 70;
f. SELECT * FROM CUSTOMER WHERE CUST_PC IN (55120, 55124, 37300, 77501);
g. SELECT * FROM CUSTOMER WHERE CUST_TITLE LIKE 'B%';
h. SELECT * FROM CUSTOMER WHERE CUST_TITLE LIKE '%r';
i. SELECT * FROM CUSTOMER WHERE CUST_PC LIKE '_5%';
j. SELECT * FROM LEAD WHERE EXPECTED_DATE > '2015-06-01';
k. UPDATE LEAD SET AMOUNT = (AMOUNT/2) + AMOUNT;
SELECT * FROM LEAD WHERE AMOUNT > 25000 ORDER BY AMOUNT DESC
l. SELECT CUST_TITLE, CUST_STATE, CUST_PC FROM CUSTOMER ORDER BY CUST_STATE,
CUST_PC DESC;
m. SELECT COUNT(*) FROM SLPRS;
n. SELECT COUNT(SLPRS_TELEPHONE) FROM SLPRS;
o. SELECT COUNT(DISTINCT(CUSTOMER_ID)) AS
NO_OF_DIFFERENT_CUSTOMER_LEADS_BELONG FROM LEAD;
p. SELECT COUNT(DISTINCT(CUSTOMER_ID)) AS
NO_OF_DIFFERENT_CUSTOMER_LEADS_BELONG, COUNT(DISTINCT(SLPRS_ID)) AS
NO_OF_DIFFERENT_SALESPERSON FROM LEAD;
q. SELECT SUM(AMOUNT) FROM LEAD WHERE POSSIBILITY > 65;
r. SELECT SUM(AMOUNT), AVG(AMOUNT), COUNT(LEAD_ID) FROM LEAD WHERE STATUS = 'I';

s. THE PROBLEM IS THAT THERE IS NO VALUE TO COMPARE TO GET THE MAX AMOUNT AND
THEN DISPLAY IT. WE WILL SOLVE IT BY USING SUB QUERY
SELECT LEAD_ID, AMOUNT FROM LEAD WHERE AMOUNT = (SELECT MAX(AMOUNT)
FROM LEAD);
t. SELECT EXPECTED_DATE FROM LEAD WHERE STATUS = 'S' OR STATUS = 'I' ORDER BY
EXPECTED_DATE ASC;
PART 2
1. CREATE TABLE STATEMENTS
''
CREATE TABLE SLPRS
(
SLPRD_ID INT PRIMARY KEY,
SLPRS_FNAME VARCHAR(30),
SLPRS_LNAME VARCHAR(30),
SLPRS_TELEPHONE DOUBLE,
SLPRS_DATE DATE
);
CREATE TABLE CUSTOMER
(
CUST_ID INT PRIMARY KEY,
CUST_TITLE VARCHAR(30),
CUST_TELEPHONE DOUBLE,
CUST_ADDRESS VARCHAR(50),
CUST_CITY VARCHAR(20),
CUST_STATE VARCHAR(30),
CUST_PC NUMBER(5,0)
);
CREATE TABLE LEAD
(
LEAD_ID INT PRIMARY KEY,
CUSTOMER_ID INT,
SLPRS_ID INT,
LDATE DATE,
EXPECTED_DATE DATE,
AMOUNT DOUBLE,

POSSIBILITY INT,
STATUS CHAR(1),
CONSTRAINT CHK_1 CHECK (STATUS IN ('F', 'S', 'I')),
CONSTRAINT L_FK1 FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUST_ID) ON
DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT L_FK2 FOREIGN KEY(SLPRS_ID) REFERENCES SLPRS(SLPRS_ID) ON DELETE
CASCADE ON UPDATE CASCADE
);
a. INSERT INTO LEAD(LEAD_ID, CUSTOMER_ID, SLPRS_ID, LDATE, EXPECTED_DATE, AMOUNT,
POSSIBILITY, STATUS) VALUES (8, 1007, 137, '2016-08-08', '2016-08-12', 13000, 50, 'I');
b. INSERT INTO LEAD(LEAD_ID, CUSTOMER_ID, SLPRS_ID, EXPECTED_DATE, POSSIBILITY,
STATUS) VALUES (9, 1003, 137, '2016-12-12', 50, 'I');
NO THIS WILL NOT BE POSSIBLE IF THE AMOUT IS SET TO NOT NULL
c. IT WILL THROW AN ERROR OF DUPLICATE PRIMARY KEY. THIS IS SO BECAUSE PRIMARY KEY
CANNOT BE DUPLICATE AND A RECORD OF ID = 7 IS ALREADY PRESENT.
d. DELETE * FROM LEAD WHERE POSSIBILITY = 70;
e. DELETE * FROM LEAD WHERE STATUS = 'I' ;
f. DELETE * FROM LEAD WHERE YEAR(LDATE) = '2015';
g. DELETE * FROM LEAD WHERE YEAR(LDATE) = '2015' AND STATUS = 'F';
1 ROW WILL BE DELETED.
h. DELETE * FROM LEAD WHERE POSSIBILITY = 50;
3 ROWS WILL BE DELETED.
i. DELETE * FROM LEAD WHERE POSSIBILITY = 30;
IF THERE ARE NO ROWS PRESENT, THE MESSAGE SHOWS ZERO(0) ROWS DELETED.
j. DELETE * FROM LEAD WHERE CUSTOMER_ID <> 1009 AND SLPRS_ID = 237;
NO ROWS DELETED
k. DELETE * FROM LEAD WHERE EXPECTED_DATE < '2016-01-01' OR AMOUNT < 25000;
l. TRUNCATE TABLE LEAD;
m.

PART 3
a. UPDATE CUSTOMER SET CUST_TITLE = 'AMI' WHERE CUST_ID = 1008;
b. UPDATE CUSTOMER SET CUST_PC = 57120 WHERE CUST_PC = 55120;
c. UPDATE CUSTOMER SET CUST_PC = 32377 WHERE CUST_PC IS NULL;
d. UPDATE CUSTOMER SET CUST_PC = 77777 WHERE CUST_PC IS NOT NULL;
e. UPDATE CUSTOMER SET CUST_STATE = 'VA' WHERE CUST_PC = 77648;
f. UPDATE CUSTOMER SET CUST_TELEPHONE = 33333333 WHERE CUST_TITLE LIKE 'A
%';
g. UPDATE CUSTOMER SET CUST_TELEPHONE = 33333333 WHERE CUST_TITLE LIKE
'%r';
h. UPDATE CUSTOMER SET CUST_TELEPHONE = 33333333 WHERE CUST_TELEPHONE
LIKE '_4%';
i. UPDATE CUSTOMER SET CUST_ADDRESS = '57 Jump str.' WHERE CUST_CITY = 'Los
Angeles' and CUST_STATE = 'CA';
3 ROWS WILL BE UPDATED
j. UPDATE CUSTOMER SET CUST_ADDRESS = '57 Jump str.' WHERE CUST_CITY = 'Los
Angeles' and CUST_STATE = 'TX';
9 ROWS WILL BE UPDATED
PART 4
a.
i. 2 ROWS WILL BE AFFECTED

ii. 1 ROW WILL BE AFFECTED
iii. 2 ROWS WILL BE AFFECTED
iv. 2 ROWS WILL BE AFFECTED
b.
i. The operations will be rejected by the system. And error message will show up.
ii. First we need to delete the corresponding SLPRS_ID and CUSTOMER_ID from
lead table then we can delete the rows from customer and SLPRS tables.
iii. The operations will be rejected by the system. And error message will show up.
iv. SLPRS_ID update will be restricted and CUSTOMER_ID update will be executed.
1 row will be affected.
v. SLPRS_ID update will be restricted and CUSTOMER_ID update will be executed.
1 row will be affected.