First SQL Lecture Queries
BME B59itsLu
sql
a year ago
3.8 kB
8
Indexable
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;
Editor is loading...
Leave a Comment