First SQL Lecture Queries

CREATE TABLE Department (Name varchar(30) not null, foundation_year int not null);
DROP TABLE Department;
CREATE TABLE Department (Name varchar(30) primary key not null, foundation_year int not null);
CREATE TABLE Department2 (Name varchar(30) not null primary key, foundation_year int not null);
select * from Department;
select * from Department2;
DROP TABLE Department2;
ALTER TABLE Department ADD Director varchar(30);
SELECT * from Department;
ALTER TABLE Department DROP Director;
Select * from Department;
INSERT into Department VALUES ('Chemical', 2002);
INSERT into Department VALUES ('Electronic', 2003);
INSERT into Department VALUES ('Mechanical', 2004);
INSERT into Department (foundation_year, Name) VALUES (2005, 'Civil');
SELECT * from Department;
DELETE from Department WHERE Name = 'Civil';
SELECT * from Department;
SELECT foundation_year FROM Department WHERE Name = 'Mechanical';
CREATE TABLE Experiment (Code int primary key not null, Name varchar(30) not null, Date varchar(30) not null);
INSERT into Experiment VALUES (012, 'Carbamide', '3/3/2003');
INSERT into Experiment VALUES (234, 'Hydro Engine', '5/8/2004');
INSERT into Experiment VALUES (541, 'Super Semi-Conductor', '7/6/2005');
SELECT * from Department, Experiment;
SELECT * from Department;
SELECT * from Experiment;
CREATE TABLE Researcher (Name varchar(30) not null, Surname varchar(30) not null, Father_Name varchar(30) not null, Rank char(1) not null, Birthday varchar(30) not null);
CREATE TABLE Assistant (Name varchar(30) not null, Surname varchar(30) not null, Father_Name varchar(30) not null, ID int primary key not null, Birthday varchar(30) not null);
INSERT into Researcher VALUES ('Joe', 'Robbins', 'Alan', 'A', '12/2/60');
INSERT into Researcher VALUES ('Tim', 'Brown', 'Thomas', 'B', '14/6/68');
INSERT into Researcher VALUES ('Helen', 'Brown', 'Chris', 'C', '25/5/75');
INSERT into Assistant (Name, Surname, Father_Name, ID, Birthday) VALUES ('John', 'Cole', 'George', 2324, '12/3/84');
INSERT into Assistant (Name, Surname, Father_Name, ID, Birthday) VALUES ('Ryan', 'Farmer', 'Phillip', 2448, '13/9/85');
INSERT into Assistant (Name, Surname, Father_Name, ID, Birthday) VALUES ('John', 'Farmer', 'David', 2587, '21/8/86');
SELECT * from Researcher;
SELECT * from Assistant;
SELECT Researcher.Surname, Assistant.Surname FROM Researcher, Assistant WHERE (Researcher.Rank = 'A') AND (Assistant.ID < 2400);
SELECT Researcher.Surname, Assistant.Surname FROM Assistant, Researcher WHERE (Researcher.Rank = 'A') AND (Assistant.ID < 2400);
SELECT * from Assistant WHERE (ID > 2000) AND (ID < 2500);
SELECT * from Assistant WHERE (ID between 2000 and 2500);
UPDATE Department SET foundation_year = 2003 WHERE Name = 'Chemical';
SELECT * from Department;
UPDATE Assistant SET Surname = 'Farmer' WHERE ID = 2448;
SELECT * from Assistant;
SELECT Surname, ID from Assistant WHERE ID > 2400 ORDER BY Assistant.Surname;
SELECT * from Assistant;
SELECT ID from Assistant WHERE ID > 2400 ORDER BY Surname;
SELECT Surname, ID from Assistant WHERE ID > 2400 ORDER BY Surname, Name;
SELECT * from Assistant ORDER BY ID DESC;
SELECT Name, Surname from Assistant WHERE Name LIKE 'J%';
SELECT Name, Surname from Assistant WHERE Name NOT LIKE 'J%';
SELECT A.Surname, R.Surname FROM Researcher AS R, Assistant AS A WHERE (R.Rank = 'A') AND (A.ID < 2400);
CREATE TABLE Accounts (Surname varchar(30) not null, Deposit int not null, bankID int primary key not null);
INSERT into Accounts VALUES ('Jones', 16000, 1);
INSERT into Accounts VALUES ('Brown', 12000, 2);
INSERT into Accounts VALUES ('Evans', 25000, 3);
SELECT * from Accounts;
SELECT Surname, Deposit*0.05 as interest FROM Accounts WHERE Deposit > 15000;
SELECT Surname from Researcher;
SELECT DISTINCT Surname from Researcher;