BEGIN TRANSACTION; /* Create a table called NAMES */ CREATE TABLE NAMES(Id integer PRIMARY KEY, first_name text, last_name text, favclass text, lunch integer); /* Create few records in this table */ INSERT INTO NAMES VALUES(1,"Jayden","Wilkins", "Ewald",1); INSERT INTO NAMES VALUES(2,"Antron","Rowland", "Gym", 3); INSERT INTO NAMES VALUES(3,"Swaay", "Taylor", "Algebra 2", 3); INSERT INTO NAMES VALUES(4,"Naya-leigh", "Williams", "Irby", 2); INSERT INTO NAMES VALUES(5,"Gibby", "Gibson", "Irby", 1); INSERT INTO NAMES VALUES(6,"Justin", "Aviles", "Gym", 1); INSERT INTO NAMES VALUES(7,"Tokobe", "Norfleet","Irby", 2) INSERT INTO NAMES VALUES(8,"Samantha","kemp", "English",1) INSERT INTO NAMES VALUES(9,"Julia Birchett", JROTC 3,1) COMMIT; /* Display all the records from the table */ SELECT * FROM NAMES;
BEGIN TRANSACTION; /* Create a table called names */ CREATE TABLE Students(Id integer PRIMARY KEY, first_name text, last_name text, favclass text, lunch integer); /* Create few records in this table */ INSERT INTO NAMES VALUES(1,"Jayden", "Wilkins", "Ewald", 1); INSERT INTO NAMES VALUES(2,"Antron", "Rowland", "Gym", 3); INSERT INTO NAMES VALUES(3,"Swaay", "Taylor", "Algebra", 3); INSERT INTO NAMES VALUES(4,"Naya-leigh", "Williams", "Irby", 3); INSERT INTO NAMES VALUES(5,"Gibby", "Gibson", "Irby", 1); INSERT INTO NAMES VALUES(6,"Tokobe","Norfleet", "Irby", 2) INSERT INTO NAMES VALUES(7,"Julia", "Birchett", "Kanney", 1) INSERT INTO NAMES VALUES(8,"Justin", "Aviles", "Irby", 1) INSERT INTO NAMES VALUES(9,"Samantha", "Kemp", "English", 1) COMMIT; /* Display all the records from the table */ SELECT * FROM NAMES;
BEGIN TRANSACTION; /* Create a table called Students */ CREATE TABLE Students(Id integer PRIMARY KEY, firstname text, lastname text, grade integer, lunch integer, favclass text); /* Create few records in this table */ INSERT INTO Students VALUES(1,'Jonathan','Gibson', 12, 1, 'Database Design'); INSERT INTO Students VALUES(2,'Antron','Rowland', 11, 3, 'Drivers Ed'); INSERT INTO Students VALUES(3,'Naya-Leigh','Williams', 10, 2, 'Database Design'); INSERT INTO Students VALUES(4,'Izayah','Taylor', 10, 3, 'Algebra II'); INSERT INTO Students VALUES(5,'Jayden','Wilkins', 11, 1, 'Culinary Arts'); INSERT INTO Students VALUES(6,'To-Kobe','Norfleet', 10, 2, 'Database Design'); INSERT INTO Students VALUES(7,'Justin','Aviles', 11, 1, 'Health & PE'); INSERT INTO Students VALUES(8,'Samantha','Kemp', 11, 1, 'English 11'); INSERT INTO Students VALUES(9, 'Julia','Birchett', 10, 1, 'JROTC'); COMMIT; /* Display all the records from the table */ SELECT COUNT(*) AS "NUM People" FROM Students;
BEGIN TRANSACTION; /* Create a table called Studnets */ CREATE TABLE Students(Id integer PRIMARY KEY, firstname text, lastname text, grade integer, lunch integer, favoriteclass text); /* Create few records in this table */ INSERT INTO Students VALUES(1,'Justin', 'Avilez', 11, 1, 'Parker'); INSERT INTO Students VALUES(2,'Ant', 'Rowland', 11, 3, 'Moedecki'); INSERT INTO Students VALUES(3,'Jayden', 'Wilkins', 11, 1, 'Irby'); INSERT INTO Students VALUES(4,'swaay', 'Taylor', 10, 3, 'Elliot'); INSERT INTO Students VALUES(5,'Gibby', 'Gibson', 12, 1, 'Irby'); INSERT INTO Students VALUES(6,'Samantha', 'Kemp', 11, 1, 'Gibson'); INSERT INTO Students VALUES(7,'Julia', 'Birchett', 10, 1, 'Colonel Kanney'); INSERT INTO Students VALUES(8,'Tokobe', 'Norfleet', 10, 2, 'Irby'); INSERT INTO Students VALUES(9, 'Naya-Leigh', 'Williams', 10, 2, 'Irby'); COMMIT; /* Display all the records from the table */ SELECT * FROM Students;
CREATE TABLE mytable(
App_No INTEGER NOT NULL PRIMARY KEY
,Type VARCHAR(3) NOT NULL
,App_Date DATE NOT NULL
,Status VARCHAR(25) NOT NULL
,FRU_Interview_Scheduled VARCHAR(14) NOT NULL
,Drug_Test VARCHAR(14) NOT NULL
,WAV_Course VARCHAR(14) NOT NULL
,Defensive_Driving VARCHAR(14) NOT NULL
,Driver_Exam VARCHAR(14) NOT NULL
,Medical_Clearance_Form VARCHAR(14) NOT NULL
,Other_Requirements VARCHAR(63) NOT NULL
,Last_Updated VARCHAR(22) NOT NULL
);
CREATE TABLE Medallion_Drivers_Active (
[License_Number] INT,
[Name] NVARCHAR(19),
[Type] NVARCHAR(21),
[Expiration_Date] DATETIME,
[Last_Date_Updated] DATETIME,
[Last_Time_Updated] NVARCHAR(5)
);
INSERT INTO Medallion_Drivers_Active VALUES
(484742,'ADJO,KOIDJE,C','MEDALLION TAXI DRIVER','2019-08-21 00:00:00','2019-07-02 00:00:00','13:20'),
(851407,'SHEIKH,YASEEN,M','MEDALLION TAXI DRIVER','2020-11-15 00:00:00','2019-07-02 00:00:00','13:20'),
(488884,'BHATTI,MOHAMMAD,N,A','MEDALLION TAXI DRIVER','2019-08-28 00:00:00','2019-07-02 00:00:00','13:20'),
(476162,'IRFAN,MUHAMMAD','MEDALLION TAXI DRIVER','2020-05-02 00:00:00','2019-07-02 00:00:00','13:20'),
(856043,'ALMONTE,JUNIOR,O','MEDALLION TAXI DRIVER','2022-02-07 00:00:00','2019-07-02 00:00:00','13:20'),
(848272,'SINGH,AJAIB','MEDALLION TAXI DRIVER','2020-03-05 00:00:00','2019-07-02 00:00:00','13:20'),
(856090,'AHMAD,SHAHBAZ','MEDALLION TAXI DRIVER','2020-03-09 00:00:00','2019-07-02 00:00:00','13:20'),
(890023,'DELAROSA,DAGOBERTO','MEDALLION TAXI DRIVER','2020-11-04 00:00:00','2019-07-02 00:00:00','13:20'),
(338966,'HIRSCH,CHARLES','MEDALLION TAXI DRIVER','2019-07-28 00:00:00','2019-07-02 00:00:00','13:20'),
(367221,'MURCIA,JORGE,E','MEDALLION TAXI DRIVER','2020-10-17 00:00:00','2019-07-02 00:00:00','13:20'),
(408394,'PAYEN,GUY','MEDALLION TAXI DRIVER','2020-02-28 00:00:00','2019-07-02 00:00:00','13:20'),
(428512,'URBANCZYK,ZYGMUNT','MEDALLION TAXI DRIVER','2019-10-23 00:00:00','2019-07-02 00:00:00','13:20'),
(436045,'PAUL,PINDER','MEDALLION TAXI DRIVER','2020-11-16 00:00:00','2019-07-02 00:00:00','13:20'),
(440335,'KHEDR,ELSAYED','MEDALLION TAXI DRIVER','2019-06-16 00:00:00','2019-07-02 00:00:00','13:20'),
(443199,'JANJUA,RANJIT,S','MEDALLION TAXI DRIVER','2020-12-15 00:00:00','2019-07-02 00:00:00','13:20'),
(486035,'BUTT,SHAHZAD','MEDALLION TAXI DRIVER','2020-10-14 00:00:00','2019-07-02 00:00:00','13:20'),
(490953,'SANGARE,MOUSSA','MEDALLION TAXI DRIVER','2020-11-28 00:00:00','2019-07-02 00:00:00','13:20'),
(449639,'JACEK,WOJCIECH','MEDALLION TAXI DRIVER','2020-10-24 00:00:00','2019-07-02 00:00:00','13:20'),
(490556,'SHABBIR,MUHAMMAD','MEDALLION TAXI DRIVER','2019-10-18 00:00:00','2019-07-02 00:00:00','13:20');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5929201,'HDR','12/11/2018','Approved - License Issued','Not Applicable','Complete','Complete','Complete','Complete','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5928664,'HDR','12/07/2018','Incomplete','Not Applicable','Complete','Complete','Complete','Needed','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5765828,'HDR','02/14/2017','Incomplete','Not Applicable','Needed','Needed','Needed','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5932604,'HDR','01/17/2019','Under Review','Not Applicable','Complete','Complete','Complete','Complete','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5934044,'HDR','02/05/2019','Incomplete','Not Applicable','Complete','Needed','Needed','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5931825,'HDR','01/07/2019','Incomplete','Not Applicable','Complete','Needed','Complete','Needed','Needed','Fingerprints needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5930672,'HDR','12/26/2018','Incomplete','Not Applicable','Complete','Complete','Complete','Needed','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5930405,'HDR','12/23/2018','Approved - License Issued','Not Applicable','Complete','Complete','Complete','Complete','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5927712,'HDR','11/28/2018','Incomplete','Not Applicable','Needed','Complete','Needed','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5930940,'HDR','12/28/2018','Incomplete','Not Applicable','Complete','Complete','Complete','Complete','Complete','Fingerprints needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5927882,'HDR','11/30/2018','Pending Fitness Interview','01/24/2019','Complete','Complete','Complete','Complete','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5931709,'HDR','01/07/2019','Incomplete','Not Applicable','Complete','Complete','Complete','Needed','Complete','Fingerprints needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5933348,'HDR','01/27/2019','Incomplete','Not Applicable','Complete','Complete','Complete','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5933664,'HDR','01/30/2019','Incomplete','Not Applicable','Complete','Needed','Needed','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5933802,'HDR','02/01/2019','Incomplete','Not Applicable','Complete','Needed','Needed','Needed','Needed','Copy of DMV license needed','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5926525,'HDR','11/14/2018','Incomplete','Not Applicable','Complete','Needed','Complete','Needed','Needed','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5925444,'HDR','11/01/2018','Approved - License Issued','Not Applicable','Complete','Complete','Complete','Complete','Complete','Not Applicable','02/08/2019 06:00:24 AM');
INSERT INTO mytable(App_No,Type,App_Date,Status,FRU_Interview_Scheduled,Drug_Test,WAV_Course,Defensive_Driving,Driver_Exam,Medical_Clearance_Form,Other_Requirements,Last_Updated) VALUES (5934096,'HDR','02/06/2019','Incomplete','Not Applicable','Needed','Needed','Needed','Needed','Needed','Fingerprints needed; Copy of DMV license needed','02/08/2019 06:00:24 AM');
SELECT "MY TABLE INFORMATION";
SELECT "";
SELECT "";
SELECT "Medallion_Drivers_Active";
SELECT "";
SELECT "";
SELECT "mytable - Where Clause";
SELECT "";
SELECT "";
SELECT "mytable - Multiple Tables";
SELECT "";
SELECT "";
SELECT "mytable - Multiple Tables with Group By Clause";
SELECT "";
SELECT "";
SELECT "mytable - Multiple Tables with Order By Clause";
SELECT "";
CREATE TABLE customer
(
id INT,
firstname NVARCHAR(40) NOT NULL,
lastname NVARCHAR(40) NOT NULL,
city NVARCHAR(40) NULL,
country NVARCHAR(40) NULL,
phone NVARCHAR(20) NULL
);
INSERT INTO [customer]
([id],
[firstname],
[lastname],
[city],
[country],
[phone])
VALUES (1,
'Maria',
'Anders',
'Berlin',
'Germany',
'030-0074321');
INSERT INTO [customer]
([id],
[firstname],
[lastname],
[city],
[country],
[phone])
VALUES (2,
'Ana',
'Trujillo',
'México D.F.',
'Mexico',
'(5) 555-4729');
INSERT INTO [customer]
([id],
[firstname],
[lastname],
[city],
[country],
[phone])
VALUES (3,
'Antonio',
'Moreno',
'México D.F.',
'Mexico',
'(5) 555-3932');
CREATE TABLE allorders
(
id INT,
orderdate DATE NOT NULL,
ordernumber NVARCHAR(10) NULL,
customerid INT NOT NULL,
totalamount DECIMAL(12, 2) NULL
);
INSERT INTO [allorders]
([id],
[orderdate],
[customerid],
[totalamount],
[ordernumber])
VALUES ( 1,
'Jul 4 2012 12:00:00:000AM',
1,
440.00,
'542378');
INSERT INTO [allorders]
([id],
[orderdate],
[customerid],
[totalamount],
[ordernumber])
VALUES ( 2,
'Jul 5 2012 12:00:00:000AM',
1,
1863.40,
'542379');
INSERT INTO [allorders]
([id],
[orderdate],
[customerid],
[totalamount],
[ordernumber])
VALUES ( 3,
'Jul 8 2012 12:00:00:000AM',
2,
1813.00,
'542380');
INSERT INTO [allorders]
([id],
[orderdate],
[customerid],
[totalamount],
[ordernumber])
VALUES ( 4,
'Jul 8 2012 12:00:00:000AM',
3,
670.80,
'500');
CREATE TABLE orderitem
(
id INT,
orderid INT NOT NULL,
productid INT NOT NULL,
unitprice DECIMAL(12, 2) NOT NULL,
quantity INT NOT NULL
);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES ( 1,
1,
11,
14.00,
12);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (2,
1,
42,
9.80,
10);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (3,
1,
72,
34.80,
5);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (4,
2,
14,
18.60,
9);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (5,
2,
51,
42.40,
40);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (6,
3,
41,
7.70,
10);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (7,
3,
51,
42.40,
35);
INSERT INTO [orderitem]
([id],
[orderid],
[productid],
[unitprice],
[quantity])
VALUES (8,
3,
65,
16.80,
15);
SELECT "table Section";
SELECT "where clause Section";
SELECT "OrderBy Section";
SELECT "JOIN Section";
SELECT "Multiple JOIN Section";
CREATE TABLE Clients (
CLIENTNUMBER INTEGER PRIMARY KEY, -- needs to be primary key for foriegn_keys to work
FIRSTNAME VARCHAR(20),
SURNAME VARCHAR(20),
EMAIL VARCHAR(100),
PHONE VARCHAR(20)
);
CREATE TABLE Loans (
ACCOUNTNUMBER INT, --A unique integer to identify this account
CLIENTNUMBER INT, -- An integer to identify the client (clients may have more than one account)
STARTDATE DATETIME, -- The time that this account was created
STARTMONTH INT, -- The month for which the first repayment is due (201805 means May 2018)
TERM INT, -- Over how many months the loan must be repaid
REMAINING_TERM INT, -- How many months remain
PRINCIPALDEBT NUMERIC(11, 2), -- The size of the initial loan
ACCOUNTLIMIT NUMERIC(11, 2), --
BALANCE NUMERIC(11, 2), -- How much is currently owed
STATUS VARCHAR(11), -- Human readable status - e.g. "PAID OFF", "ARREARS", "NORMAL"
FOREIGN KEY (CLIENTNUMBER) REFERENCES Clients(CLIENTNUMBER) -- CLIENTNUMBER is the foreign key from Clients table.
);
INSERT INTO Clients VALUES (1, 'Robert', 'Warren', '[email protected]', '(251) 546-9442');
INSERT INTO Clients VALUES (2, 'Vincent', 'Brown', '[email protected]', '(125) 546-4478');
INSERT INTO Clients VALUES (3, 'Janet', 'Prettyman', '[email protected]', '(949) 569-4371');
INSERT INTO Clients VALUES (4, 'Martina', 'Kershner', '[email protected]', '(630) 446-8851');
INSERT INTO Clients VALUES (5, 'Tony', 'Schroeder', '[email protected]', '(226) 906-2721');
INSERT INTO Clients VALUES (6, 'Harold', 'Grimes', '[email protected]', '(671) 925-1352');
INSERT INTO Loans VALUES (1,1,'2017-11-01 10:00:00', 201712, 36, 35, 10000.00, 15000.00, 9800.00, 'NORMAL');
INSERT INTO Loans VALUES (2,2,'2018-01-01 10:00:00', 201802, 24, 24, 1000.00, 1500.00, 1000.00, 'NORMAL');
INSERT INTO Loans VALUES (3,1,'2016-11-01 10:00:00', 201612, 12, -3, 2000.00, 15000.00, 4985.12, 'ARREARS');
INSERT INTO Loans VALUES (4,3,'2018-01-01 10:00:00', 201802, 24, 24, 3500.00, 5000.00, 1300.00, 'NORMAL');
INSERT INTO Loans VALUES (5,4,'2017-11-01 10:00:00', 201712, 12, 35, 10000.00, 15000.00, 0.00, 'PAID OFF');
INSERT INTO Loans VALUES (6,5,'2018-01-01 10:00:00', 201802, 48, 24, 1000.00, 1500.00, 0.00, 'PAID OFF');
INSERT INTO Loans VALUES (7,6,'2015-11-01 10:00:00', 201512, 12, -20, 10000.00, 15000.00, 9800.00, 'Arrears');
INSERT INTO Loans VALUES (7,4,'2018-01-01 10:00:00', 201802, 12, 1, 2400.00, 3600.00, 130.00, 'NORMAL');
-- Is the data there?
SELECT 'Loans';
SELECT '----------------------------------------------------';
SELECT * FROM Loans;
SELECT '';
SELECT 'Clients';
SELECT '----------------------------------------------------';
SELECT * FROM Clients;
SELECT '1. Everyone who owes more than $5,000 on an account:';
SELECT '----------------------------------------------------';
SELECT FIRSTNAME, SURNAME, BALANCE FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE BALANCE > 5000.00;
SELECT '';
SELECT '2. Find all loans older than Jan 2017';
SELECT '----------------------------------------------------';
SELECT Clients.FIRSTNAME, Clients.SURNAME, Loans.STARTMONTH FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE Loans.STARTMONTH > 201721;
SELECT '';
SELECT '3. Find all clients who have more than one loan';
SELECT '----------------------------------------------------';
SELECT Loans.CLIENTNUMBER, Clients.FIRSTNAME, Clients.SURNAME FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
GROUP BY Loans.CLIENTNUMBER
HAVING COUNT(*) >= 2;
SELECT '';
SELECT "4. Find the total balance outstanding over all loans that aren't in arrears";
SELECT '----------------------------------------------------';
SELECT SUM(BALANCE) FROM Loans WHERE STATUS != 'ARREARS';
SELECT '';
SELECT '5. Are all account numbers unique? (How should we fix this in general)';
SELECT '----------------------------------------------------';
SELECT '';
SELECT '6. Martina has undergone gender reassignment and is now Martin';
SELECT '----------------------------------------------------';
SELECT * FROM Clients;
UPDATE Clients SET FIRSTNAME = "Martin"
WHERE FIRSTNAME = "Martina";
SELECT '#### After Modified ####';
SELECT * FROM Clients;
SELECT '';
SELECT '7. Get a list of email addresses for all clients who paid off a loan';
SELECT '----------------------------------------------------';
SELECT Clients.EMAIL From CLients JOIN Loans
ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE Loans.STATUS = 'PAID OFF';
SELECT '';
SELECT '8. Print out the largest loan for each client';
SELECT '----------------------------------------------------';
SELECT Clients.FIRSTNAME, Clients.SURNAME, SUM(Loans.BALANCE) FROM Clients
JOIN Loans ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
GROUP BY(Loans.CLIENTNUMBER);
SELECT '############################';
SELECT * FROM Loans;
create table student(
name varchar(20),
student_number number,
class number,
major varchar(20)
);
insert into student
values('smith',17,1,'cs');
insert into student
values('brown',8,2,'cs');
select * from student;
create table course(
c_name varchar(20),
c_number varchar(20),
credits number,
department varchar(20)
);
insert into course
values('intro_to_com_science','cs1310',4,'cs');
insert into course
values('Data_structures','cs3320',4,'cs');
insert into course
values('Data_base','cs1380',3,'cs');
insert into course
values('Discrete_maths','math2410',3,'math');
select * from course;
create table section(
sec_identifier number,
c_number varchar(20),
semester varchar(20),
year number,
instructor varchar(20)
);
insert into section
values(85,'math2410','fall',7,'king');
insert into section
values(92,'cs1310','fall',7,'anderson');
insert into section
values(102,'cs3320','spring',8,'knuth');
insert into section
values(112,'math2410','fall',8,'chang');
insert into section
values(119,'cs1310','fall',8,'anderson');
insert into section
values(135,'cs3380','fall',8,'stone');
select * from section;
create table grade_report(
student_number number,
sec_identifier number,
grade varchar(2));
insert into grade_report
values(17,112,'b');
insert into grade_report
values(17,119,'c');
insert into grade_report
values(8,85,'c');
insert into grade_report
values(8,92,'a');
insert into grade_report
values(8,102,'b');
insert into grade_report
values(8,135,'a');
select * from grade_report;
create table prerequisite(
c_number varchar(20),
pre_number varchar(20)
);
insert into prerequisite
values('cs3380','cs3320');
insert into prerequisite
values('cs3380','math2410');
insert into prerequisite
values('cs3320','cs1310');
select * from prerequisite;
insert into student
values('johnson',25,1,'math');
select * from student;
update student
set class=2
where name='smith';
select * from student;
insert into course
values('knowledge_engineering','cs4390',3,'cs');
select * from course;
delete from student
where name='smith';
select * from student;
select name from student
where major='cs';
select c_name from course
where c_number=(select c_number from section
where year=7 and instructor='king');
select c_number,semester,year,count(instructor)
from section where instructor='king'
group by semester,year,c_number;
BEGIN TRANSACTION; /* Create a table called NAMES */ CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text); /* Create few records in this table */ INSERT INTO NAMES VALUES(1,'Tom'); INSERT INTO NAMES VALUES(2,'Lucy'); INSERT INTO NAMES VALUES(3,'Frank'); INSERT INTO NAMES VALUES(4,'Jane'); INSERT INTO NAMES VALUES(5,'Robert'); COMMIT; /* Display all the records from the table */ SELECT * FROM NAMES; select * from names; describe table names;
CREATE TABLE DEPARTMENT
(DEPARTMENT_CODE CHAR(2) NOT NULL,
NAME VARCHAR(50),
OFFICE_PHONE NUMBER(8),
OFFICE_NUMBER NUMBER(4),
COLLEGE VARCHAR(50),
PRIMARY KEY(DEPARTMENT_CODE));
CREATE TABLE COURSE
(DEPARTMENT_CODE CHAR(2),
COURSE_NUMBER NUMBER(5) NOT NULL,
COURSE_NAME VARCHAR(50),
DESCRIPTION VARCHAR(100),
LEVEL VARCHAR(2),
NUMBER_OF_SEMESTER_HOURS NUMBER(1),
PRIMARY KEY(COURSE_NUMBER),
FOREIGN KEY(DEPARTMENT_CODE) REFERENCES DEPARTMENT(DEPARTMENT_CODE));
CREATE TABLE SECTION
(COURSE_NUMBER NUMBER(5),
SECTION_NUMBER VARCHAR(4) NOT NULL,
YEAR NUMBER(4),
SEMESTER VARCHAR(2),
INSTRUCTOR VARCHAR(50),
PRIMARY KEY(COURSE_NUMBER, SECTION_NUMBER, YEAR, SEMESTER),
FOREIGN KEY(COURSE_NUMBER) REFERENCES COURSE(COURSE_NUMBER));
CREATE TABLE STUDENT
(STUDENT_NUMBER NUMBER(8) NOT NULL,
COHORT NUMBER(4),
DEGREE PROGRAM VARCHAR(50),
F_NAME VARCHAR(20),
L_NAME VARCHAR(50),
SEX CHAR(1),
BIRTH_DATE FORMAT 'YYYY-MM-DD',
HKID VARCHAR(10),
C_PHONE_NUMBER NUMBER(8),
C_REGION VARCHAR(20),
C_STREET_ADDRESS VARCHAR(100),
C_DISTRICT VARCHAR(20),
P_PHONE_NUMBER NUMBER(8),
P_REGION VARCHAR(20),
P_STREET_ADDRESS VARCHAR(100),
P_DISTRICT VARCHAR(20),
PRIMARY KEY(STUDENT_NUMBER));
CREATE TABLE GRADE_REPORT
(STUDENT_NUMBER NUMBER(8) NOT NULL,
NUMERIC_GRADE NUMBER(3),
LETTER_GRADE VARCHAR(2),
COURSE_NUMBER NUMBER(5) NOT NULL,
SECTION_NUMBER VARCHAR(4) NOT NULL,
YEAR NUMBER(4) NOT NULL,
SEMESTER VARCHAR(2) NOT NULL,
PRIMARY KEY(STUDENT_NUMBER, COURSE_NUMBER, SECTION_NUMBER, YEAR, SEMESTER),
FOREIGN KEY(STUDENT_NUMBER) REFERENCES STUDENT(STUDENT_NUMBER),
FOREIGN KEY(COURSE_NUMBER, SECTION_NUMBER, SEMESTER, YEAR) REFERENCES SECTION(COURSE_NUMBER, SECTION_NUMBER, SEMESTER, YEAR));
CREATE TABLE DEPARTMENT_STUDENT
(STUDENT_NUMBER NUMBER(8) NOT NULL,
DEPARTMENT_CODE CHAR(2),
STUDY_TYPE CHAR(5),
PRIMARY KEY(STUDENT_NUMBER, DEPARTMENT_CODE),
FOREIGN KEY(STUDENT_NUMBER) REFERENCES STUDENT(STUDENT_NUMBER),
FOREIGN KEY(DEPARTMENT_CODE) REFERENCES DEPARTMENT(DEPARTMENT_CODE));
INSERT INTO DEPARTMENT
VALUES('CS','COMPUTER SCIENCE',31234567,3102,'CITYU');
INSERT INTO DEPARTMENT
VALUES('SM','CREATIVE MEDIA',31234850,4516,'CMC');
INSERT INTO DEPARTMENT
VALUES('TVB','DIGITAL TELEVISION AND BROADCASTING',31234339,2312,'CMC');
INSERT INTO DEPARTMENT
VALUES('EE','ELECTRONIC ENGINEERING',31234264,6023,'CCCU');
INSERT INTO COURSE
VALUES('CS',3402,'DATABASE SYSTEM','LEARNING THE CONCEPT OF DATABASE AND APPLICATION OF SQL','A2',30);
INSERT INTO COURSE
VALUES('CS',4001,'AI MACHINE LEARNING','LEARNING THE APPLICATION OF AI AND HOW THEY EFFECT IN LIFE','B3',42);
INSERT INTO COURSE
VALUES('CS',2003,'INTRODUCTION OF C++','C++ LANGUAGE COURSE FOR BEGINNERS','A1',30);
INSERT INTO COURSE
VALUES('EE',4103,'HARDWARE HACKING','USING HARDWARE TO IMPROVE OUR LIFE','B3',33);
INSERT INTO COURSE
VALUES('EE',2313,'ARDUINO BASIC II','LEARNING CONCEPT OF ARDUINO AND MAKE YOUR OWN','A1',30);
INSERT INTO COURSE
VALUES('SM',2401,'CREATIVE CODING','JAVA LANGUAGE FOR BEGINNERS','A3',33);
INSERT INTO SECTION
VALUES(3402,'C01',2018,'A','AKI AU');
INSERT INTO SECTION
VALUES(3402,'C02',2018,'A','AKI AU');
INSERT INTO SECTION
VALUES(3402,'C03',2018,'A','JENNY FU');
INSERT INTO SECTION
VALUES(3402,'C01',2019,'B','KEN CHAN');
INSERT INTO SECTION
VALUES(3402,'C02',2019,'B','AKI AU');
INSERT INTO SECTION
VALUES(2313,'TB1',2016,'A','COLMAN WONG');
INSERT INTO SECTION
VALUES(2313,'TB2',2016,'A','COLMAN WONG');
INSERT INTO SECTION
VALUES(2313,'TB1',2019,'A','MANDY WONG');
INSERT INTO SECTION
VALUES(2313,'TA3',2019,'A','AKI AU');
INSERT INTO SECTION
VALUES(2401,'CA3',2017,'B','AKI AU');
INSERT INTO SECTION
VALUES(2401,'C01',2017,'B','COLMAN WONG');
INSERT INTO STUDENT
VALUES(54840550,2017,'BA CREATIVE MEDIA','SIU MING','CHEUNG','F',1990-07-31,'Y322222(1)',21245648,'HK','RM211, 14 KAM NAM STREET','MONGKOK',21245678,'HK','RM211, 14 KAM NAM STREET','MONGKOK');
INSERT INTO STUDENT
VALUES(54840551,2017,'BA CREATIVE MEDIA','CHEUK MAN','WONG','F',1994-03-02,'Y122791(4)',21245678,'HK','FLAT A, 20/F, 152 CANNON STREET','SAI KUNG',21245678,'HK','FLAT A, 20/F, 152 CANNON STREET','SAI KUNG');
INSERT INTO STUDENT
VALUES(54840552,2017,'BSC CREATIVE MEDIA','TSZ YAN','NG','F',1994-04-13,'Y722422(2)',214453718,'HK','ROOM 34, 14 MONGKOK ROAD','MONGKOK',21245678,'HK','ROOM 34, 14 MONGKOK ROAD','MONGKOK');
INSERT INTO STUDENT
VALUES(54840553,2017,'COMPUTER SCIENCE','CAN LIN','XU','F',1995-08-21,'R326352(5)',22335675,'HK','26 SHAM SHUI ROAD','TSUEN WAN',21245678,'HK','RM211, 14 KAM NAM STREET','MONGKOK');
INSERT INTO STUDENT
VALUES(54840554,2016,'COMPUTER SCIENCE','SUI CHEONG','LEUNG','F',1996-08-29,'Y352422(4)',21255673,'HK','RM31 A,WONG HEUNG BUILDING, 52 KWAN HING STREET','KWAI HING',21245678,'HK','RM31 A,WONG HEUNG BUILDING, 52 KWAN HING STREET','KWAI HING');
INSERT INTO GRADE_REPORT
VALUES(54840550,12,'F',3402,'C01',2018,'A');
INSERT INTO GRADE_REPORT
VALUES(54840550,89,'A-',3402,'C01',2019,'B');
INSERT INTO GRADE_REPORT
VALUES(54840550,55,'C',2313,'TA3',2019,'A');
INSERT INTO GRADE_REPORT
VALUES(54840551,23,'C-',3402,'C02',2019,'B');
INSERT INTO GRADE_REPORT
VALUES(54840552,67,'B',2313,'TA3',2019,'A');
INSERT INTO GRADE_REPORT
VALUES(54840553,92,'A+',2401,'C01',2017,'B');
INSERT INTO GRADE_REPORT
VALUES(54840554,82,'B+',2401,'C01',2017,'B');
INSERT INTO GRADE_REPORT
VALUES(54840554,36,'D',2313,'TA3',2019,'A');
INSERT INTO GRADE_REPORT
VALUES(54840554,84,'B+',3402,'C01',2019,'B');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840550,'CS','MINOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840550,'CM','MAJOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840551,'CM','MAJOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840552,'EE','MINOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840552,'CM','MAJOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840553,'CS','MAJOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840553,'TVB','MINOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840553,'EE','MINOR');
INSERT INTO DEPARTMENT_STUDENT
VALUES(54840551,'CS','MAJOR');
We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy. Accept Learn more
