 CREATE TABLE Student (
studId      VARCHAR(6)     PRIMARY KEY,
lastName    VARCHAR(20)    NOT NULL   ,
firstName   VARCHAR(20)    NOT NULL   ,
major       VARCHAR(20)               ,
credits     DECIMAL(3)      DEFAULT   0,
CONSTRAINT Student_credits_cc CHECK  ((credits>=0) AND (credits <150)));


CREATE TABLE Faculty  (
facId       VARCHAR(6),
name        VARCHAR(20),
department  VARCHAR(20),
therank     VARCHAR(20),
CONSTRAINT faculty_facId_pk PRIMARY KEY (facId));


CREATE TABLE Class(
classNumber  VARCHAR(8),
facId        VARCHAR(6),
schedule     VARCHAR(8),
room         VARCHAR(6),
CONSTRAINT class_classNumber_pk PRIMARY KEY (classNumber),
CONSTRAINT class_schedule_uk  UNIQUE (schedule,room));


CREATE TABLE Enroll(
studId      VARCHAR(6),
classNumber VARCHAR(8),
grade       VARCHAR(2),
CONSTRAINT  Enroll_ClassNumber_studId_pk PRIMARY KEY(classNumber,studId),
CONSTRAINT  Enroll_ClassNumber_fk   FOREIGN KEY (classNumber) REFERENCES Class (classNumber),
CONSTRAINT  Enroll_studId_fk        FOREIGN KEY (studId)      REFERENCES Student (studId)  on delete set null);


Insert into Student values ('S1001', 'Smith', 'Tom', 'History', 90);
Insert into Student values ('S1002','Chin','Anne','Math',36);
Insert into Student values ('S1005','Lee','Perry','History',3);
Insert into Student values ('S1010','Burns','Edward','Art',63);
Insert into Student values ('S1013','McCarthy','Owen','Math',0);
Insert into Student values ('S1015','Jones','Mary','Math',42);
Insert into Student values ('S1020','Rivera','Jane','CSC',15);


Insert into Faculty values ('F101','Adams','Art','Professor');
Insert into Faculty values ('F105','Tanaka','CSC','Instructor');
Insert into Faculty values ('F110','Byrne','MATH','Assistant');
Insert into Faculty values ('F115','Smith','HISTORY','Associate');
Insert into Faculty values ('F221','Smith','CSC','Professor');


Insert into Class values ('ART103A','F101','MWF9','H221');
Insert into Class values ('CSC201A','F105','TuThF10','M110');
Insert into Class values ('CSC203A','F105','MTHF12','M110');
Insert into Class values ('HST205A','F115','MWF11','H221');
Insert into Class values ('MTH101B','F110','MTuTh9','H225');
Insert into Class values ('MTH103C','F110','MWF11','H225');


Insert into Enroll values ('S1001','ART103A','A');
Insert into Enroll values ('S1001','HST205A','C');
Insert into Enroll values ('S1002', 'ART103A','D');
Insert into Enroll values ('S1002','CSC201A','F');
Insert into Enroll values ('S1002','MTH103C','B');


Insert into Enroll values ('S1020','CSC201A','B');
Insert into Enroll values ('S1020','MTH101B','A');
Insert into Enroll(studId, classNumber) values ('S1010','ART103A');
Insert into Enroll(studId, classNumber) values ('S1010','MTH103C');


create Table customer(
id MEDIUMINT  NOT NULL AUTO_INCREMENT ,
food varchar(20) NOT NULL,
CONSTRAINT customers_id_pk PRIMARY KEY (id)
);

Insert into customer (food) values ('shrimp');
Insert into customer (food) values('chicken');
Insert into customer (food) values('pasta');
Insert into customer (food) values ('sushi');


select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'Enroll';


alter table Enroll add column status varchar(20) after grade;
alter table Enroll drop column status;

-- Example 1
-- show specific columns from table with condition
select lastName, firstName, studId, credits from Student where major = "math";


-- Example 2
-- show entire table
select * from Enroll;


-- Example 3
-- show entire table without duplicate class numbers
select distinct Enroll.classNumber from Enroll;


-- Example 4
-- you can write commands on multiple lines 
select * 
from Faculty 
where department="csc";


-- Example 5 
-- change names of columns just in result table and order records by a column
select name as FacultyName, facId as FacultyNumber
from Faculty order by name;


-- Example 6 
-- 2 different conditions in the where
select lastName, firstName from Student where major = "math" and credits > 30;


-- Example 7
-- select columns and apply conditions to the joined Student and Enroll tables
select lastName, firstName from Student, Enroll 
where classNumber = "ART103A" and Student.studId = Enroll.studId;


-- Example 8
-- find studId and grade of all students taking any class taught by faculty with facId="F110"
select studId, grade from Enroll, Class 
where Enroll.classNumber = Class.classNumber and facId = "F110";


-- Example 9 
-- find classNumber and the names and majors for all students enrolled in class taught by facId="F110"
select Class.classNumber, lastName, firstName, major from Student, Enroll, Class
where Student.studId = Enroll.studId and Enroll.classNumber = Class.classNumber 
and facId = "F110";


-- Example 10 
-- use of alieses: get list of classes that meet in same room with schedules and room num
-- here we compare a table with itself. We need 2 temp copies (alieses) of Class table
-- eliminate redundancy 
select A.classNumber, A.schedule, A.room, B.classNumber, B.schedule
from Class A, Class B where A.room = B.room and A.classNumber < B.classNumber;


-- Example 11 
-- find all combinations of student and factulty where student's major != fac dept
select s.studId, s.lastName, s.firstName, s.major, f.facId, f.name, f.department
from Student s, Faculty f where s.major <> f.department;


-- Example 12 
-- find the numbers of all classes taught by Byrne
select classNumber from Class where facId = (select facId from Faculty where name = "Byrne");


-- Example 13 
-- 2 ways to do the same thing
select name, Faculty.facId from Faculty, Class where Faculty.facId = Class.facId and Class.room="H221";
select name, facId from Faculty WHERE facId in (select facId from Class where room = "H221");


-- Example 14
select lastName, firstName, Student.studId from Student, Class, Faculty, Enroll
where Student.studId = Enroll.studId and Enroll.classNumber = Class.classNumber
and Class.facId = Faculty.facId and Faculty.facId = "F110";

select lastName, firstName, Student.studId from Student where studId in (select studId
from Enroll where classNumber in (select classNumber from Class where facId="F110"));


-- Example 15
-- find names of all students enrolled in CSC201A
select lastName, firstName from Student, Enroll where Student.studId = Enroll.studId 
and classNumber= "CSC201A";


-- Example 16
-- find names of all students not enrolled in CSC201A
select lastName, firstName from Student, Enroll where Student.studId = Enroll.studId 
and classNumber <> "CSC201A";

select lastName, firstName from Student where not exists (select * from Enroll 
where Student.studId = Enroll.studId and classNumber = "CSC201A");


-- Example 17
-- get IDs of all faculty who are assigned to history department or who teach in room H221
select Faculty.facId from Faculty, Class where Faculty.facId = Class.facId 
and Class.room = "H221" or Faculty.department= "History";

select Faculty.facId from Faculty where department = "History" union 
select facId from Class where room = "H221";


-- Example 18 
-- Part A: find total number of students enrolled in specific class "ART103A"
select count(distinct studId) from Enroll where classNumber = "ART103A";

-- Part B: find number of departments that have faculty in them 
select count(distinct department) from Faculty;

-- Part C: find sum of all the credits history majors have earned
select sum(credits) from Student where major = "History";

-- Part D: find average credits all students have
select avg(credits) from Student;

-- Part E: find student with the most credits
select studId from Student where credits = (select max(credits) from Student);

-- Part F: find names and IDs of students who have less than avg number of credits
select studId, lastName, firstName from Student where 
credits < (select avg(credits) from Student);


-- Example 19 
select 'number of credits =', credits from Student;


-- Example 20 
-- Part A: for each major, find sum of all credits the students in that major have
-- need to group students by major then do calculations on each group
select major, sum(credits) from Student group by major; 

-- Part B: for each class, show number of enrolled students
select classNumber, count(distinct studId) from Enroll group by classNumber;


-- Example 21 
-- Part A: Find all courses in which fewer than 3 students are enrolled
select classNumber from Enroll group by classNumber having count(distinct studId < 3);

-- Part B: find depts where average credits is less than total avg credits for all students
select major from Student group by major having avg(credits) < (select avg(credits) from Student);


-- Example 22 
-- get details of math courses
select * from Class where classNumber like "MTH%";


-- Example 23 
-- find the student id and classNum for students whose grades in the class are missing
select classNumber, studId from Enroll where grade is NULL;


-- UPDATE 
-- Example 1 
-- update single field of one record 
-- change major of s1020 to Music
update Student set major = "Music" where studId= "s1020";


-- Example 2 
-- update multiple fields
update Faculty set department= "Math", therank = "Assistant" where name = "Tanaka";


-- Example 3 
update Student set major= NULL where studId="s1013";


-- Example 4 
-- update all grades in a class
update Enroll set grade = "A" where classNumber= "CSC201A";


-- Example 5 
-- update all records in one table
update Student set credits = credits + 3;


-- Example 6
-- update with subquery
update Class set room = "B220" where facId = 
(select facId from Faculty where name = "Tanaka");


-- INSERT
-- Example 1
insert Faculty(facId, name, department, therank) values ("F330","Jones","CSC","Instructor");


-- Example 2 
insert into Student values ("s1030","Hunt","Alice","Art","12");


-- Example 3
-- Part A: inserrt only specific values
insert into Student(lastName, firstName, studId, credits) 
values ("Bono","Maria","s1031",0);

-- Part B: insert all values using null 
insert into Student values("s1032","Bono","Maria", NULL, 0);


-- Example 4
create table Enrollment (
  classNumber varchar(10),
  attending varchar(3));
  
insert into Enrollment select classNumber, count(*) from Enroll group by classNumber;


-- Example 5
create table Customer (
  name varchar(20),
  food varchar(20),
  orderDate date,
  orderTime timestamp);
  
insert into Customer values ("Jack","chicken",curdate(), current_timestamp());


--

created 1 year ago by Sarah Mae Baydal Bejeras

MySQL online editor

Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About MySQL

MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.

Key Features:

Open-source relational database management systems.
Reliable, very fast and easy to use database server.
Works on client-server model.
Highly Secure and Scalable
High Performance
High productivity as it uses stored procedures, triggers, views to write a highly productive code.
Supports large databases efficiently.
Supports many operating systems like Linux*,CentOS*, Solaris*,Ubuntu*,Windows*, MacOS*,FreeBSD* and others.

Syntax help

Commands

1. CREATE

CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

Example

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

2. ALTER

ALTER TABLE Table_name ADD column_name datatype;

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');

3. TRUNCATE

TRUNCATE table table_name;

4. DROP

DROP TABLE table_name;

5. RENAME

RENAME TABLE table_name1 to new_table_name1;

6. COMMENT

Single-Line Comments:

 --Line1;

Multi-Line comments:

   /* Line1,
   Line2 */

DML Commands

1. INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Note: Column names are optional.

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');

2. SELECT

SELECT column1, column2, ...
FROM table_name
[where condition];

Example

SELECT * FROM EMPLOYEE where dept ='sales';

3. UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

4. DELETE

DELETE FROM table_name where condition;

Example

DELETE from EMPLOYEE where empId='0001';

Indexes

1. CREATE INDEX

  CREATE INDEX index_name on table_name(column_name);

To Create Unique index:

  CREATE UNIQUE INDEX index_name on table_name(column_name);

2. DROP INDEX

DROP INDEX index_name ON table_name;

Views

1. Create a View

Creating a View:
CREATE VIEW View_name AS 
Query;

2. How to call view

SELECT * FROM View_name;

3. Altering a View

ALTER View View_name AS 
Query;

4. Deleting a View

DROP VIEW View_name;

Triggers

1. Create a Trigger

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */

2. Drop a Trigger

DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures

1. Create a Stored Procedure

CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;

2. How to call Stored procedure

CALL sp_name;

3. How to delete stored procedure

DROP PROCEDURE sp_name;

Joins

1. INNER JOIN

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

2. LEFT JOIN

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN

SELECT select_list from TABLE1 CROSS JOIN TABLE2;