1 Context write SQL statements for given questions in order to get familiar to basic
1 Context
write SQL statements for given questions in order to get familiar to basic
SQL data manipulation and definition statements by(Using MYSQL Application)
using insert
using update
using alter
create table
create view
1. DEFINE TABLE AUTHORS WITH COLUMNS AUTHORID INT AND AUTHORNAME VARCHAR(30)
-- ADD COLUMN LEVEL PRIMARY KEY CONSTRAINT FOR AUTHORID
-- ADD COLUMN LEVEL NOT NULL CONSTRAINT FOR AUTHORNAME
-- (NOT NULL CAN ONLY BE DEFINED AT COLUMN LEVEL)
-- INSERT SOME VALUES TO AUTHORS TABLE
INSERT INTO AUTHORS VALUES(3,'İLBER ORTAYLI');
INSERT INTO AUTHORS(AUTHORNAME, AUTHORID) VALUES ('JULES VERNE',4);
INSERT INTO AUTHORS VALUES(5,'STEPHEN HAWKING');
INSERT INTO AUTHORS VALUES(6,'GEORGE ORWELL');
-- CHECK RECORDS ON TABLE
SELECT * FROM AUTHORS;
-- 2. DEFINE TABLE PUBLISHERS WITH COLUMNS
-- PUBLISHERID INT AND PUBLISHERNAME VARCHAR(30)
-- ADD TABLE LEVEL PRIMARY KEY CONSTRAINT FOR PUBLISHERID
-- ADD TABLE LEVEL UNIQUE CONSTRAINT FOR PUBLISHERNAME
-- INSERT SOME VALUES TO PUBLISHERS TABLE
INSERT INTO PUBLISHERS VALUES(1,'ALFA YAYINCILIK');
INSERT INTO PUBLISHERS VALUES(2,'KRONIK KITAP');
-- CHECK IF NEXT QUERY EXECUTE
INSERT INTO PUBLISHERS VALUES(3,'KRONIK KITAP');
-- INSERT ANOTHER ROW
INSERT INTO PUBLISHERS VALUES(3,'CAN YAYINLARI');
-- CHECK RECORDS ON TABLE
SELECT * FROM PUBLISHERS;
-- 3. ADD NEW COLUMN TO PUBLISHERS TABLE
-- CITY VARCHAR(20)
-- 4. SET CITY OF PUBLISHER CAN YAYINLARI TO İSTANBUL
-- WHAT IF WHERE CLAUSE IS FORGOTTEN
UPDATE PUBLISHERS SET CITY='İSTANBUL';
-- CHECK TABLE
SELECT * FROM PUBLISHERS;
-- 5. DEFINE TABLE NAMED BOOKS WITH COLUMNS
-- BOOKID INT, TITLE VARCHAR(30), AUTHORID ?, PUBLISHERID ?,
-- GENRE VARCHAR(20)
-- ADD FOREIGN KEY CONSTRAINT PUBLISHERID
-- ADD FOREIGN KEY CONSTRAINT AUTHORID WITH ON DELETE CASCADE
-- ON DELETE CASCADE (when a referenced parent table row is removed
-- all the child are removed automatically)
-- 6. ALTER TABLE BOOKS AND ADD NEW PRIMARY KEY CONSTRAINT FOR BOOKID
-- 7. ADD NEW COLUMN PRIZE NUMERIC(5,2) FOR BOOKS TABLE
-- 8. ALTER TABLE BOOKS AND ADD NEW CHECK CONSTRAINT FOR PRIZE>=5.00
-- INSERT SOME VALUES TO BOOKS TABLE
-- ID:1,TITLE:HAYVAN ÇİFTLİĞİ,AUTHOR:GEORGE ORWELL ,
-- PUBLISHER:CAN YAYINLARI, GENRE:SCIENCE FICTION,PRIZE=10.00
INSERT INTO BOOKS VALUES(1,'HAYVAN ÇİFTLİĞİ',6,3,'SCIENCE FICTION',10.00);
-- ID:2,TITLE:KARA DELİKLER, AUTHORID:5, PUBLISHERID:1
-- WE DONT KNOW GENRE AND PRIZE
INSERT INTO BOOKS(BOOKID,TITLE,AUTHORID,PUBLISHERID) VALUES(2,'KARA DELİKLER',5,1);
-- CHECK IF DOES NEXT QUERY WORK
INSERT INTO BOOKS VALUES(3,'GAZİ MUSTAFA KEMAL ATATÜRK',3,2,'BIOGRAPHY',4.00);
-- SAME ABOVE WITH PRIZE 15.00
INSERT INTO BOOKS VALUES(3,'GAZİ MUSTAFA KEMAL ATATÜRK',3,2,'BIOGRAPHY',15.00);
-- ID:4,TITLE:ZAMANIN KISA TARİHİ,AUTHORID:5, PUBLISHERID:1,
-- GENRE:POPULAR SCIENCE,PRIZE:10.00
INSERT INTO BOOKS VALUES(4,'ZAMANIN KISA TARİHİ',5,1,'POPULAR SCIENCE',10.00);
-- CHECK TABLE
SELECT * FROM BOOKS;
-- 9. ADD NEW COLUMN NATION CHAR(2) FOR AUTHORS TABLE
-- UPDATE AUTHOR NATIONALITIES
-- TR,FR AND EN FOR RELATED AUTHORS
UPDATE AUTHORS SET NATION='TR' WHERE AUTHORID=3;
UPDATE AUTHORS SET NATION='FR' WHERE AUTHORID=4;
UPDATE AUTHORS SET NATION='EN' WHERE AUTHORID=6;
UPDATE AUTHORS SET NATION='EN' WHERE AUTHORID=5;
SELECT * FROM AUTHORS;
-- DELETE PUBLISHER 'CAN YAYINLARI'
-- IS IT POSSIBLE
DELETE FROM PUBLISHERS WHERE PUBLISHERNAME='CAN YAYINLARI';
-- DELETE AUTHOR 'GEORGE ORWELL'
-- IS IT POSSIBLE
DELETE FROM AUTHORS WHERE AUTHORNAME='GEORGE ORWELL';
-- SEE EFFECTS ON OTHER TABLES
SELECT * FROM BOOKS;
SELECT * FROM PUBLISHERS;
SELECT * FROM AUTHORS;
-- 10. CREATE VIEW BOOKSVIEW WITH AUTHORS, BOOK TITLES AND PRIZE
-- CHECK VIEW
SELECT * FROM BOOKSVIEW;
-- UPDATE PRIZE OF BOOK KARA DELİKLER TO 7.50 IN BOOKSVIEW
UPDATE BOOKSVIEW SET PRIZE=7.50 WHERE TITLE='KARA DELİKLER';
-- INCREASE PRIZE OF ALL BOOKS BY %8 IN BOOKSVIEW
UPDATE BOOKSVIEW SET PRIZE=PRIZE*1.08;
-- CHECK VIEW
SELECT * FROM BOOKSVIEW;
SELECT * FROM BOOKS;
-- DOES NEXT QUERY EXECUTE
INSERT INTO BOOKSVIEW VALUES('1984','GEORGE ORWELL',10.00);
-- 11. CREATE VIEW PUBVIEW OF PUBLISHERS WITH ID AND NAME
-- INSERT VALUE INTO PUBVIEW
-- PUBLISHERID:7 , PUBLISHERNAME:DENEME1
-- IS IT POSSIBLE
INSERT INTO PUBVIEW VALUES (7,'DENEME1');
-- INSERT VALUE INTO PUBVIEW
-- PUBLISHERID:3 , PUBLISHERNAME:DENEME2
-- IS IT POSSIBLE
INSERT INTO PUBVIEW VALUES (3,'DENEME2');
-- SEE EFFECTS ON BASE TABLE AND VIEW
SELECT * FROM PUBVIEW;
SELECT * FROM PUBLISHERS;
-- DELETE ROW YOU HAVE ADDED
DELETE FROM PUBVIEW WHERE PUBLISHERNAME='DENEME1';
-- SEE EFFECTS ON BASE TABLE AND VIEW
SELECT * FROM PUBVIEW;
SELECT * FROM PUBLISHERS;
Step by step
Solved in 2 steps with 2 images
