SQL statements for given questions in order to get familiar to basic SQL data manipulation and definition statements by ˆ using insert ˆ using update ˆ using alter ˆ create table ˆ create view 2 Problem description Questions are listed below. See template for required insert, delete and update statements. You will also find some comments and queries in order to see some possible problems. ˆ 1. DEFINE TABLE AUTHORS WITH COLUMNS AUTHORID INT AND AUTHORNAME VARCHAR(30),ADD COLUMN LEVEL PRIMARY KEY CONSTRAINT FOR AUTHO

write SQL statements for given questions in order to get familiar to basic
SQL data manipulation and definition statements by
ˆ using insert
ˆ using update
ˆ using alter
ˆ create table
ˆ create view
2 Problem description
Questions are listed below. See template for required insert, delete and update statements. You will also find
some comments and queries in order to see some possible problems.
ˆ 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
ˆ 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
ˆ 3. ADD NEW COLUMN(CITY VARCHAR(20)) TO PUBLISHERS TABLE
ˆ 4. SET CITY OF PUBLISHER CAN YAYINLARI TO ˙ISTANBUL
ˆ 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
ˆ 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
ˆ 9. ADD NEW COLUMN (NATION CHAR(2)) FOR AUTHORS TABLE
ˆ 10. CREATE VIEW BOOKSVIEW WITH AUTHORS, BOOK TITLES AND PRIZE
ˆ 11. CREATE VIEW PUBVIEW OF PUBLISHERS WITH ID AND NAME

demo.sql

DROP TABLE IF EXISTS EMP;

CREATE TABLE EMP

(EMPNO DECIMAL(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR DECIMAL(4), HIREDATE DATE, SAL DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO DECIMAL(2));

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'); INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'); INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30'); INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30'); INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10'); INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10'); INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30'); INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20'); INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30'); INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10'); DROP TABLE IF EXISTS DEPT; CREATE TABLE DEPT (DEPTNO DECIMAL(2), DNAME VARCHAR(14), LOC VARCHAR(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); DROP TABLE IF EXISTS BONUS; CREATE TABLE BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL DECIMAL, COMM DECIMAL); DROP TABLE IF EXISTS SALGRADE; CREATE TABLE SALGRADE (GRADE DECIMAL, LOSAL DECIMAL, HISAL DECIMAL); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);