Write the suitable MySQL code do the following: 1. Create a database called MyTa

Question

Write the suitable MySQL code do the following:

1. Create a database called MyTables.

2. Create a table called Materials witht the following attributes :

a) Name: text of size 25, primary key .

b) Type : text of size 20 .

c) studentsNo : integer of size 2 .

d) Mandatory : Boolean.

e) MDate : Date.

3) Delete all materials that have MDate less than 15/5/2016, if number of records in the table > 20.

4) Change the field MDate to become current date + 5 days, for those Materials with type "CIS".

NOTE: Questions Numer 3 and 4 are very important .

Explanation / Answer

1. create database MyTables;

2. create table Materials(
Name VARCHAR(25) NOT NULL,
Type VARCHAR(20) NOT NULL,
studentsNo INTEGER [2],
Mandatory not null default 0,
MDate DATE
PRIMARY KEY (Name)
);

3. Delete from Materials where (select count(Name) from Materials)>20 and MDate > '2016-05-15'

4 . UPDATE Materials
SET MDate= DATE_ADD(CURDATE(),INTERVAL 5 DAY)
WHERE Type='CIS'

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.