Scribd
Upload
46 views

Mis SQL

The document contains SQL statements to create database tables for accounts, order details, orders, and products, along with constraints and indexes. It then provides sample insert statements to populate the accounts and products tables with initial data. The document also contains some sample SQL queries against courses and related database tables.

Uploaded by

Phước Nguyễn
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
46 views

Mis SQL

The document contains SQL statements to create database tables for accounts, order details, orders, and products, along with constraints and indexes. It then provides sample insert statements to populate the accounts and products tables with initial data. The document also contains some sample SQL queries against courses and related database tables.

Uploaded by

Phước Nguyễn
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

create table Accounts (

User_Name varchar(20) not null,


Active bit not null,
Password varchar(20) not null,
User_Role varchar(20) not null,
primary key (User_Name)
);

create table Order_Details (


ID varchar(50) not null,
Amount double precision not null,
Price double precision not null,
Quanity integer not null,
ORDER_ID varchar(50) not null,
PRODUCT_ID varchar(20) not null,
primary key (ID)
);

create table Orders (


ID varchar(50) not null,
Amount double precision not null,
Customer_Address varchar(255) not null,
Customer_Email varchar(128) not null,
Customer_Name varchar(255) not null,
Customer_Phone varchar(128) not null,
Order_Date datetime not null,
Order_Num integer not null,
primary key (ID)
);

create table Products (


Code varchar(20) not null,
Create_Date datetime not null,
Image longblob,
Name varchar(255) not null,
Price double precision not null,
primary key (Code)
);

alter table Orders


add constraint UK_sxhpvsj665kmi4f7jdu9d2791 unique
(Order_Num);

alter table Order_Details


add constraint ORDER_DETAIL_ORD_FK
foreign key (ORDER_ID)
references Orders (ID);
alter table Order_Details
add constraint ORDER_DETAIL_PROD_FK
foreign key (PRODUCT_ID)
references Products (Code);
---------------------------------------
insert into Accounts (USER_NAME, ACTIVE, PASSWORD, USER_ROLE)
values ('employee1', 1, '123', 'EMPLOYEE');

insert into Accounts (USER_NAME, ACTIVE, PASSWORD, USER_ROLE)


values ('manager1', 1, '123', 'MANAGER');

----------------
insert into products (CODE, NAME, PRICE, CREATE_DATE)
values ('S001', 'Core Java', 100, current_timestamp() );

insert into products (CODE, NAME, PRICE, CREATE_DATE)


values ('S002', 'Spring for Beginners', 50,
current_timestamp() );

insert into products (CODE, NAME, PRICE, CREATE_DATE)


values ('S003', 'Swift for Beginners', 120,
current_timestamp() );

insert into products (CODE, NAME, PRICE, CREATE_DATE)


values ('S004', 'Oracle XML Parser', 120, current_timestamp() );

insert into products (CODE, NAME, PRICE, CREATE_DATE)


values ('S005', 'CSharp Tutorial for Beginers', 110,
current_timestamp() );
Database quiz:

1. Get all courses having lab sessions

SELECT * FROM course WHERE credit_lab >0;

2. Get all courses of IEM department

SELECT C.name, D.name FROM course as C


INNER JOIN course_department as CD ON C.id = CD.course_id
INNER JOIN department as D ON D.id = CD.department_id
WHERE D.name = ‘IEM’;
3. Count number of courses handled by each instructor

SELECT I.name, count(CI.course_id) FROM Course_instructor as CI


INNER JOIN instructor as I ON I.id = CI.instructor_id
GROUP BY I.name;

4. Fill all instructors handing more than 3 courses

SELECT I.name FROM Course_instructor as CI


INNER JOIN instructor as I ON I.id = CI.instructor_id
WHERE count(CI.course_id) > 3
GROUP BY I.name;

Example:

1. Alice wants to send a message to Bob. And Alice wants to make sure that only Bob can read the
message. What should Alice do?

Ans:
Alice has a Pub_Alice, Priv_Alice, and Pub_Bob.
Bob has a Pub_Bob, Priv_Bob, and Pub_Alice.
Alice should use the public key of Bob (Pub_Bob) to encrypt the message.
Pub_Bob(message)

2. In which situation Alice will use the Priv_Alice to encrypt the message? Priv_Alice(message)
Ans:
In the case that Alice want to make sure that only Alice can write/adjust the message.
3. How can Alice be the one who writes the message and only FB can read the message?
Ans:
Alice uses her private key (Priv_Alice) to encrypt a message. Then, she uses the public key Facebook to
encrypt the above encrypted message again.
Pub_FB(Priv_Alice(message))

4. How to make sure Alice is the one established the message?

Ans: by certification authorities.

You might also like