SQL EXE 1 Latihan Database ERD 2 ANS PDF

Preview

Summary

Download SQL EXE 1 Latihan Database ERD 2 ANS PDF

Description

ICT200

09.12.2020

STRUCTURE QUERY LANGUAGE SQL LAB: DATABASE LATIHAN Consider the COMPANY and the STUDENT tables below:

COMPANY

STUDENT

ICT200

09.12.2020

Write SQL command: Create database latihan CREATE DATABASE seroja Create database connection CONNECT TO seroja

a)

Do the following task. i.

CREATE TABLE INNOVATOR CREATE TABLE INNOVATOR(INNOVATOR_ID VARCHAR(14)NOT NULL ,INNOVATOR_NAME VARCHAR(20),INNOVATOR_BIRTHPLACE VARCHAR(20),INNOVATOR_NATIONALITY VARCHAR(20),INNOVATOR_DOB VARCHAR(20),INNOVATOR_DEATH VARCHAR(20),STYLES_OF_ART VARCHAR(20),PRIMARY KEY(INNOVATOR_ID))

CREATE TABLE INNOVATION CREATE TABLE INNOVATION(INNOVATION_ID VARCHAR(14)NOT NULL ,INNOVATION_YEAR VARCHAR(10),INNOVATION_TITLE VARCHAR(15), INNOVATION_PRICE VARCHAR(15), INNOVATION_CATEGORY VARCHAR(15),PRIMARY KEY(INNOVATION_ID),FOREIGN KEY(INNOVATOR_ID) REFERENCES INNOVATOR(INNOVATOR_ID)) CREATE TABLE INNOVATION(INNOVATION_ID VARCHAR(14) NOT NULL,INNOVATION_YEAR VARCHAR(20),INNOVATION_TITLE VARCHAR(20),INNOVATION_PRICE VARCHAR(10),INNOVATION_CATEGORY VARCHAR(20),PRIMARY KEY(INNOVATION_ID),FOREIGN KEY(INNOVATOR_ID) REFERENCES INNOVATOR(INNOVATOR_ID)) ii. iii.

CREATE TABLE INVOLVED CREATE TABLE INVOLVED( EMPLOYEE_ID VARCHAR(14) NOT NULL, EXPOSITION_ID VARCHAR(14) NOT NULL, EXTRA_HOURS VARCHAR(10), DATE_INVOLVED VARCHAR(10), PRIMARY KEY(EMPLOYEE_ID,EXPOSITION_ID),

ICT200

09.12.2020

FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID), FOREIGN KEY(EXPOSITION_ID) REFERENCES EXPOSITION(EXPOSITION_ID))

Insert data as shown in table company a. Single value without attribute name {used when value is sequence with attribute name} INSERT INTO COMPANY VALUES ('C001','Telekom Malaysia','Sarawak',300.00)

b. Single value with attribute name {used when value is not sequence with attribute name} INSERT INTO COMPANY (company_name, company_id,state,allowance) VALUES ('Tenaga Nasional Berhad','C002','Selangor',300.00) c. Multiple value INSERT INTO INNOVATOR VALUES('I002','ISMAIL ALJAZARI','JAZIRA','IRAQ','26 NOVEMBER 1350', '2 DECEMBER 1429','INDUSTRIAL'), INSERT INTO INNOVATOR VALUES ('I004','MOHAMED BIN HAMZAH’,'JOHOR','MALAYSIA','5 MARCH 1918’, '19 FEBRUARY 1993','ABSTRACT') INSERT INTO INNOVATOR VALUES('I004','LATIFF BIN MOHIDIN’,'LENGGENG','MALAYSIA','20 AUGUST 1941',' ','ABSTRACT') INSERT INTO INNOVATOR VALUES ('I005','VINCENT VAN GOGH','ZUNDERT','NETHERLAND','29 JULY 1890',' ','ABSTRACT') INSERT INTO INNOVATOR VALUES ('I006','ZULKIFLI HARON','PASIR MAS','MALAYSIA','2 JANUARY 1959',' ','INDUSTRIAL') INSERT INTO INNOVATOR VALUES ('I006','ZULKIFLI HARON','PASIR MAS','MALAYSIA','2 JANUARY 1959',' ','INDUSTRIAL'))

iv.

MULTIPLE VALUES INSERT INTO INNOVATOR(INNOVATOR_ID,INNOVATOR_NAME,INNOVATOR_BIRTHPLACE,INNOVATO R_NATIONALITY,INNOVATOR_DOB,INNOVATOR_DEATH,STYLES_OF_ART)VALUES('I 008','BETTE NESMITH GRAHAM','TEXAS','UNITED STATES','23 MARCH 1924','12 MAY 1990','INDUSTRIAL'),('I009','LEONARDO DI CAPRIO','ANCHIANO,'ITALY','15 APRIL 1452','2 MAY 1959','INDUSTRIAL'),('I010','MASASHI KISHIMOTO','OSAKA','JAPAN','8 NOVEMBER 1974',' ','MODERN'); Insert data as shown in table student INSERT INTO student VALUES ('S101','Aisyah','CS110','C001'), ('S102','Iskandar','CS113','C003'), ('S103','Norain','CS226','C001'),('S104','Nazar','CS110','C004'), ('S105','Sulaiman','CS113','C006'),('S106','Nora','CS110','C005'), ('S107','Alias','CS113','C007'),('S108','Suhaila','CS226',null)

ICT200

b)

09.12.2020

List students who are on which company and then order by student’s program. (3 marks) SELECT S.STUDENT_NAME, C.COMPANY_NAME FROM COMPANY AS C, STUDENT AS S WHERE C.COMPANY_ID = S.COMPANY_ID ORDER BY PROGRAM

c)

List students who receive allowance more than 500. (2 marks) SELECT S.STUDENT_ID, S.STUDENT_NAME, C.ALLOWANCE FROM COMPANY AS C, STUDENT AS S WHERE S.STUDENT_ID = C.COMPANY_ID AND ALLOWANCE > 500

d)

Count number of students who receives allowance less than 350. SELECT COUNT(STUDENT_ID) FROM COMPANY AS C, STUDENT AS S WHERE C.COMPANY_ID = S.COMPANY_ID AND C.ALLOWANCE < 350 How many students assign to each company? Company name No of student

SELECT C.COMPANY_NAME, COUNT(STUDENT_ID) FROM COMPANY AS C, STUDENT AS S WHERE C.COMPANY_ID = S.COMPANY_ID GROUP BY C.COMPANY_ID (3 marks) e)

Increase 50% allowance for company Celcom (M) Sdn Bhd from current allowance. UPDATE COMPANY SET ALLOWANCE = ALLOWANCE * 1.5 WHERE COMPANY_ID = ‘C003’ (3 marks)

f)

List student(s) who are not assigned to any company. SELECT * FROM STUDENT WHERE COMPANY_ID IS NULL 2 marks)

g)

Summarize the number of students by company name and list only the company which the number of is greater or equal to 2. (4 marks) SELECT C.COMPANY_NAME, COUNT(STUDENT_ID) FROM COMPANY AS C, STUDENT AS S WHERE C.COMPANY_ID = S.COMPANY_ID GROUP BY C.COMPANY_ID

ICT200

09.12.2020

HAVING COUNT(STUDENT_ID) >= 2...