-- Creating the database
--skip-grant-tables
CREATE DATABASE IF NOT EXISTS university_database;
-- Switch to the created database
USE university_database;
-- Creating database users
CREATE USER 'adminU'@'localhost' IDENTIFIED BY 'Adminpassword24';
-- FLUSH PRIVILEGES;
CREATE USER 'staffU'@'localhost' IDENTIFIED BY '@Staffpassword24';
-- FLUSH PRIVILEGES;
CREATE USER 'studentU'@'localhost' IDENTIFIED BY '@Studentpassword24';
-- FLUSH PRIVILEGES;
-- Granting privileges to users
GRANT ALL PRIVILEGES ON university_database.* TO 'adminU'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON university_database.* TO 'staffU'@'localhost';
GRANT SELECT ON university_database.* TO 'studentU'@'localhost';
SHOW GRANTS;
FLUSH PRIVILEGES;
-- Create Department Table
CREATE TABLE Department (
DepartmentID INT AUTO_INCREMENT PRIMARY KEY,
DepartmentName VARCHAR(100),
DepartmentPhoneNumber VARCHAR(50),
Budget DECIMAL(10, 2),
HeadOfDepartmentID INT,
DepartmentEmail VARCHAR(100)
);
-- Inserting sample data into Department table
INSERT INTO Department (DepartmentName, DepartmentPhoneNumber, Budget, HeadOfDepartmentID, DepartmentEmail)
VALUES
('Computer Science', '123-456-7890', 1000000.00, 101, '[email protected]'),
('Mathematics', '987-654-3210', 800000.00, 102, '[email protected]'),
('Physics', '111-222-3333', 600000.00, 103, '[email protected]'),
('Chemistry', '444-555-6666', 750000.00, 104, '[email protected]'),
('Biology', '777-888-9999', 700000.00, 105, '[email protected]'),
('History', '888-999-0000', 550000.00, 106, '[email protected]'),
('Literature', '333-444-5555', 600000.00, 107, '[email protected]'),
('Economics', '222-333-4444', 900000.00, 108, '[email protected]'),
('Engineering', '555-666-7777', 1200000.00, 109, '[email protected]'),
('Global Challenges', '111-222-3333', 850000.00, 111, '[email protected]'),
('Software Engineering', '444-555-6666', 950000.00, 112, '[email protected]'),
('International Business and Trade', '777-888-9999', 800000.00, 113, '[email protected]'),
('Psychology', '666-777-8888', 650000.00, 110, '[email protected]');
-- Create AdministrativeStaff Table
CREATE TABLE AdministrativeStaff (
StaffID INT PRIMARY KEY,
DepartmentID INT,
HireDate DATE,
PhoneNumber VARCHAR(50),
Position VARCHAR(100),
LastName VARCHAR(50),
FirstName VARCHAR(50),
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);
-- Retrieve information from Department table
SELECT * FROM Department;
-- Retrieve information from AdministrativeStaff table
SELECT * FROM AdministrativeStaff;
-- Retrieve information of a specific department by DepartmentID
SELECT * FROM Department WHERE DepartmentID = 1;
-- Retrieve information of administrative staff members in a specific department
SELECT * FROM AdministrativeStaff WHERE DepartmentID = 1;
-- Create Facilitator Table
CREATE TABLE Facilitator (
FacilitatorID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Email VARCHAR(255),
DepartmentID INT,
ProfessionalTitle VARCHAR(255),
Biography TEXT,
Qualifications TEXT,
PhoneNumber VARCHAR(20),
Specialization VARCHAR(255),
OfficeHours VARCHAR(255),
Nationality VARCHAR(255),
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);
-- Create Classroom Table
CREATE TABLE Classroom (
ClassroomID INT PRIMARY KEY,
RoomName VARCHAR(255),
BuildingName VARCHAR(255),
Capacity INT
);
-- Create Equipment Table
CREATE TABLE Equipment (
EquipmentID INT PRIMARY KEY,
EquipmentName VARCHAR(255),
EquipmentCategory VARCHAR(255),
ConditionStatus VARCHAR(255),
ClassroomID INT,
FOREIGN KEY (ClassroomID) REFERENCES Classroom(ClassroomID)
);
-- Create Course Table
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(255),
DepartmentID INT,
MaxEnrollment INT,
LanguageOfInstruction VARCHAR(50),
Credits DECIMAL(4, 2),
SemesterOffered VARCHAR(255),
AssessmentMethod VARCHAR(255),
CourseDescription TEXT,
FacilitatorID INT,
ClassroomID INT,
Schedule VARCHAR(255),
Level VARCHAR(50),
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID),
FOREIGN KEY (FacilitatorID) REFERENCES Facilitator(FacilitatorID),
FOREIGN KEY (ClassroomID) REFERENCES Classroom(ClassroomID)
);
-- Create Student Table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Phone_No VARCHAR(20),
Date_Of_Birth DATE,
Age INT,
Nationality VARCHAR(255),
Email VARCHAR(255),
Gender VARCHAR(10)
);
-- Create FinancialAid Table
CREATE TABLE FinancialAid (
TransactionID INT PRIMARY KEY,
StudentID INT,
FinancialAidType VARCHAR(255),
Amount DECIMAL(10, 2),
DateIssued DATE,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);
-- Create CourseEnrollment Table
CREATE TABLE CourseEnrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
CompletionDate DATE,
Attendance INT,
Status VARCHAR(50),
Grade VARCHAR(10),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
-- Inserting sample data into Department table
INSERT INTO Department (DepartmentName, DepartmentPhoneNumber, Budget, HeadOfDepartmentID, DepartmentEmail)
VALUES
('Computer Science', '123-456-7890', 1000000.00, 101, '[email protected]'),
('Mathematics', '987-654-3210', 800000.00, 102, '[email protected]'),
('Physics', '111-222-3333', 600000.00, 103, '[email protected]'),
('Chemistry', '444-555-6666', 750000.00, 104, '[email protected]'),
('Biology', '777-888-9999', 700000.00, 105, '[email protected]'),
('History', '888-999-0000', 550000.00, 106, '[email protected]'),
('Literature', '333-444-5555', 600000.00, 107, '[email protected]'),
('Economics', '222-333-4444', 900000.00, 108, '[email protected]'),
('Engineering', '555-666-7777', 1200000.00, 109, '[email protected]'),
('Global Challenges', '111-222-3333', 850000.00, 111, '[email protected]'),
('Software Engineering', '444-555-6666', 950000.00, 112, '[email protected]'),
('International Business and Trade', '777-888-9999', 800000.00, 113, '[email protected]'),
('Psychology', '666-777-8888', 650000.00, 110, '[email protected]');
-- Inserting sample data into AdministrativeStaff table
INSERT INTO AdministrativeStaff (StaffID, DepartmentID, HireDate, PhoneNumber, Position, LastName, FirstName)
VALUES
(101, 1, '2020-01-15', '111-222-3333', 'Head of Department', 'Smith', 'John'),
(102, 2, '2019-08-20', '444-555-6666', 'Assistant Professor', 'Johnson', 'Alice'),
(103, 3, '2018-05-10', '777-888-9999', 'Secretary', 'Williams', 'Emma'),
(104, 4, '2017-12-01', '333-444-5555', 'Dean', 'Jones', 'Michael'),
(105, 5, '2016-09-15', '666-777-8888', 'Associate Professor', 'Brown', 'Sophia'),
(106, 6, '2015-06-20', '999-000-1111', 'Coordinator', 'Davis', 'Matthew'),
(107, 7, '2014-03-10', '222-333-4444', 'Counselor', 'Miller', 'Olivia'),
(108, 8, '2013-10-05', '555-666-7777', 'Assistant Dean', 'Wilson', 'Daniel'),
(109, 9, '2012-07-15', '888-999-0000', 'Registrar', 'Martinez', 'Ava'),
(110, 10, '2011-04-20', '000-111-2222', 'Director', 'Garcia', 'Ethan'),
(111, 11, '2010-01-10', '111-222-3333', 'Supervisor', 'Hernandez', 'Isabella'),
(112, 12, '2009-08-05', '444-555-6666', 'Manager', 'Lopez', 'Alexander');
-- Inserting sample data into Facilitator table
INSERT INTO Facilitator (FacilitatorID, FirstName, LastName, Email, DepartmentID, ProfessionalTitle, Biography, Qualifications, PhoneNumber, Specialization, OfficeHours, Nationality)
VALUES
(201, 'Michael', 'Brown', '[email protected]', 1, 'Senior Lecturer', 'Dr. Michael Brown has a Ph.D. in Computer Science...', 'Ph.D. Computer Science', '777-888-9999', 'Database Management', 'Mon-Fri 9am-5pm', 'USA'),
(202, 'Emily', 'Davis', '[email protected]', 2, 'Lecturer', 'Dr. Emily Davis holds a Ph.D. in Mathematics...', 'Ph.D. Mathematics', '222-333-4444', 'Algebra', 'Tue-Thu 10am-2pm', 'UK'),
(203, 'John', 'Smith', '[email protected]', 5, 'Associate Professor', 'Dr. John Smith has a Ph.D. in Physics...', 'Ph.D. Physics', '333-444-5555', 'Quantum Mechanics', 'Wed-Fri 9am-12pm', 'Canada'),
(204, 'Alice', 'Johnson', '[email protected]', 3, 'Assistant Professor', 'Dr. Alice Johnson holds a Ph.D. in Chemistry...', 'Ph.D. Chemistry', '444-555-6666', 'Organic Chemistry', 'Mon-Wed 1pm-4pm', 'Australia'),
(205, 'David', 'Williams', '[email protected]', 2, 'Lecturer', 'Dr. David Williams holds a Ph.D. in Biology...', 'Ph.D. Biology', '555-666-7777', 'Genetics', 'Tue-Thu 9am-12pm', 'USA'),
(206, 'Sarah', 'Jones', '[email protected]', 6, 'Professor', 'Dr. Sarah Jones has a Ph.D. in History...', 'Ph.D. History', '666-777-8888', 'World History', 'Mon-Wed 10am-1pm', 'UK'),
(207, 'Matthew', 'Brown', '[email protected]', 3, 'Associate Professor', 'Dr. Matthew Brown holds a Ph.D. in Literature...', 'Ph.D. Literature', '777-888-9999', 'English Literature', 'Wed-Fri 9am-12pm', 'Canada'),
(208, 'Olivia', 'Martinez', '[email protected]', 9, 'Lecturer', 'Dr. Olivia Martinez holds a Ph.D. in Economics...', 'Ph.D. Economics', '888-999-0000', 'Macroeconomics', 'Mon-Wed 1pm-4pm', 'USA'),
(209, 'Daniel', 'Garcia', '[email protected]', 8, 'Assistant Professor', 'Dr. Daniel Garcia holds a Ph.D. in Psychology...', 'Ph.D. Psychology', '999-000-1111', 'Clinical Psychology', 'Tue-Thu 9am-12pm', 'Australia'),
(210, 'Sophia', 'Lopez', '[email protected]', 3, 'Lecturer', 'Dr. Sophia Lopez holds a Ph.D. in Sociology...', 'Ph.D. Sociology', '111-222-3333', 'Criminology', 'Mon-Wed 10am-1pm', 'UK'),
(211, 'Matthew', 'Rodriguez', '[email protected]', 11, 'Assistant Professor', 'Dr. Matthew Rodriguez holds a Ph.D. in Political Science...', 'Ph.D. Political Science', '222-333-4444', 'International Relations', 'Tue-Thu 1pm-4pm', 'USA'),
(212, 'Emily', 'Gonzalez', '[email protected]', 12, 'Lecturer', 'Dr. Emily Gonzalez holds a Ph.D. in Linguistics...', 'Ph.D. Linguistics', '333-444-5555', 'Syntax and Semantics', 'Mon-Wed 9am-12pm', 'Canada');
-- Inserting sample data into Classroom table
INSERT INTO Classroom (ClassroomID, RoomName, BuildingName, Capacity)
VALUES
(1, 'Kenya', 'Social Commons', 50),
(2, 'Nigeria', 'Enterprise Commons', 40),
(3, 'Ghana', 'Leadership Centre', 60),
(4, 'South Africa', 'Social Commons', 55),
(5, 'Egypt', 'Enterprise Commons', 45),
(6, 'Algeria', 'Leadership Centre', 70),
(7, 'Morocco', 'Social Commons', 52),
(8, 'Tanzania', 'Enterprise Commons', 48),
(9, 'Uganda', 'Leadership Centre', 65),
(10, 'Angola', 'Social Commons', 58),
(11, 'Zimbabwe', 'Enterprise Commons', 42),
(12, 'Ethiopia', 'Leadership Centre', 62);
-- Inserting sample data into Equipment table
INSERT INTO Equipment (EquipmentID, EquipmentName, EquipmentCategory, ConditionStatus, ClassroomID)
VALUES
(1, 'Projector', 'Electronics', 'Good', 1),
(2, 'Whiteboard', 'Stationery', 'Fair', 2),
(3, 'Desk', 'Furniture', 'Excellent', 3),
(4, 'Computer', 'Electronics', 'Good', 4),
(5, 'Chair', 'Furniture', 'Fair', 5),
(6, 'Air Conditioner', 'Electronics', 'Excellent', 6),
(7, 'Printer', 'Electronics', 'Fair', 7),
(8, 'Scanner', 'Electronics', 'Good', 8),
(9, 'Table', 'Furniture', 'Excellent', 9),
(10, 'Speaker', 'Electronics', 'Good', 10),
(11, 'Microphone', 'Electronics', 'Fair', 11),
(12, 'Podium', 'Furniture', 'Good', 12);
-- Inserting sample data into Course table
INSERT INTO Course (CourseID, CourseName, DepartmentID, MaxEnrollment, LanguageOfInstruction, Credits, SemesterOffered, AssessmentMethod, CourseDescription, FacilitatorID, ClassroomID, Schedule, Level)
VALUES
(1, 'Introduction to Computer Science', 1, 50, 'English', 3.00, 'Fall', 'Exams, Assignments', 'This course provides an introduction to computer science...', 201, 1, 'Mon-Wed-Fri 9am-11am', 'Beginner'),
(2, 'Advanced Mathematics', 2, 40, 'English', 4.00, 'Spring', 'Exams, Projects', 'This course covers advanced topics in mathematics...', 202, 2, 'Tue-Thu 1pm-3pm', 'Advanced'),
(3, 'Physics for Engineers', 3, 60, 'English', 3.50, 'Fall', 'Exams, Labs', 'This course introduces fundamental principles of physics...', 203, 3, 'Mon-Wed-Fri 10am-12pm', 'Intermediate'),
(4, 'Organic Chemistry', 4, 55, 'English', 4.00, 'Spring', 'Exams, Lab Reports', 'This course covers the study of organic compounds...', 204, 4, 'Tue-Thu 9am-11am', 'Advanced'),
(5, 'Genetics and Evolution', 5, 45, 'English', 3.50, 'Fall', 'Exams, Presentations', 'This course explores the principles of genetics and evolution...', 205, 5, 'Mon-Wed 1pm-3pm', 'Intermediate'),
(6, 'World History', 6, 70, 'English', 4.00, 'Spring', 'Exams, Essays', 'This course surveys world history from ancient civilizations to the present...', 206, 6, 'Tue-Thu 10am-12pm', 'Advanced'),
(7, 'English Literature', 7, 52, 'English', 3.50, 'Fall', 'Exams, Literary Analysis', 'This course explores major works of English literature...', 207, 7, 'Mon-Wed 9am-11am', 'Intermediate'),
(8, 'Macroeconomics', 8, 48, 'English', 4.00, 'Spring', 'Exams, Economic Reports', 'This course covers principles of macroeconomics...', 208, 8, 'Tue-Thu 1pm-3pm', 'Advanced'),
(9, 'Clinical Psychology', 9, 65, 'English', 3.50, 'Fall', 'Exams, Case Studies', 'This course covers the study of clinical psychology...', 209, 9, 'Mon-Wed 10am-12pm', 'Intermediate'),
(10, 'Criminology', 10, 58, 'English', 4.00, 'Spring', 'Exams, Research Papers', 'This course explores the study of crime and criminal behavior...', 210, 10, 'Tue-Thu 9am-11am', 'Advanced'),
(11, 'International Relations', 11, 42, 'English', 3.50, 'Fall', 'Exams, Policy Analysis', 'This course covers the study of international relations...', 211, 11, 'Mon-Wed 1pm-3pm', 'Intermediate'),
(12, 'Syntax and Semantics', 12, 62, 'English', 4.00, 'Spring', 'Exams, Syntax Analysis', 'This course explores the syntax and semantics of natural languages...', 212, 12, 'Tue-Thu 10am-12pm', 'Advanced');
-- Inserting sample data into Student table with age derived from Date_Of_Birth
INSERT INTO Student (StudentID, First_Name, Last_Name, Phone_No, Date_Of_Birth, Nationality, Email, Gender)
VALUES
(1, 'John', 'Doe', '123-456-7890', '2000-05-15', 'USA', '[email protected]', 'Male'),
(2, 'Alice', 'Smith', '987-654-3210', '2001-08-20', 'UK', '[email protected]', 'Female'),
(3, 'Michael', 'Johnson', '555-123-4567', '1999-03-10', 'Canada', '[email protected]', 'Male'),
(4, 'Emily', 'Brown', '444-987-6543', '2000-11-25', 'Australia', '[email protected]', 'Female'),
(5, 'Sarah', 'Jones', '321-654-9870', '2001-06-30', 'USA', '[email protected]', 'Female'),
(6, 'David', 'Wilson', '987-654-3210', '1999-09-05', 'UK', '[email protected]', 'Male'),
(7, 'Sophia', 'Garcia', '444-321-6540', '2000-04-18', 'Canada', '[email protected]', 'Female'),
(8, 'Matthew', 'Martinez', '123-987-6540', '2001-10-12', 'USA', '[email protected]', 'Male'),
(9, 'Olivia', 'Hernandez', '789-123-4560', '1999-07-25', 'Australia', '[email protected]', 'Female'),
(10, 'Daniel', 'Lopez', '987-654-3210', '2000-02-09', 'UK', '[email protected]', 'Male'),
(11, 'Isabella', 'Gonzalez', '321-654-9870', '2001-12-05', 'USA', '[email protected]', 'Female'),
(12, 'Ethan', 'Rodriguez', '987-654-3210', '1999-08-20', 'Canada', '[email protected]', 'Male');
-- Update Age based on Date_Of_Birth
UPDATE Student SET Age = TIMESTAMPDIFF(YEAR, Date_Of_Birth, CURDATE());
-- Inserting sample data into FinancialAid table
INSERT INTO FinancialAid (TransactionID, StudentID, FinancialAidType, Amount, DateIssued)
VALUES
(1, 1, 'Scholarship', 5000.00, '2023-09-15'),
(2, 2, 'Grant', 3000.00, '2023-08-20'),
(3, 3, 'Loan', 7000.00, '2023-10-10'),
(4, 4, 'Scholarship', 4500.00, '2023-11-05'),
(5, 5, 'Grant', 2500.00, '2023-09-30'),
(6, 6, 'Loan', 8000.00, '2023-08-25'),
(7, 7, 'Scholarship', 5500.00, '2023-10-12'),
(8, 8, 'Grant', 3500.00, '2023-11-20'),
(9, 9, 'Loan', 6000.00, '2023-09-05'),
(10, 10, 'Scholarship', 5000.00, '2023-08-10'),
(11, 11, 'Grant', 3000.00, '2023-10-25'),
(12, 12, 'Loan', 7000.00, '2023-09-15');
-- Inserting sample data into CourseEnrollment table
INSERT INTO CourseEnrollment (EnrollmentID, StudentID, CourseID, EnrollmentDate, CompletionDate, Attendance, Status, Grade)
VALUES
(1, 1, 1, '2023-09-15', '2023-12-20', 90, 'Completed', 'A'),
(2, 2, 2, '2023-08-20', '2023-12-15', 95, 'Completed', 'A+'),
(3, 3, 3, '2023-10-10', '2023-12-18', 85, 'Completed', 'B+'),
(4, 4, 4, '2023-11-05', '2023-12-22', 80, 'Completed', 'B'),
(5, 5, 5, '2023-09-30', '2023-12-21', 92, 'Completed', 'A-'),
(6, 6, 6, '2023-08-25', '2023-12-17', 88, 'Completed', 'B'),
(13, 1, 3, '2024-01-10', NULL, 75, 'In-progress', NULL),
(14, 2, 5, '2024-01-15', NULL, 80, 'In-progress', NULL),
(15, 3, 7, '2024-01-20', NULL, 70, 'In-progress', NULL),
(16, 4, 9, '2024-01-25', NULL, 85, 'In-progress', NULL),
(17, 5, 11, '2024-02-01', NULL, 90, 'In-progress', NULL),
(7, 7, 7, '2023-10-12', '2023-12-19', 94, 'Completed', 'A+'),
(8, 8, 8, '2023-11-20', '2023-12-20', 82, 'Completed', 'B-'),
(9, 9, 9, '2023-09-05', '2023-12-23', 87, 'Completed', 'B+'),
(10, 10, 10, '2023-08-10', '2023-12-16', 91, 'Completed', 'A-'),
(11, 11, 11, '2023-10-25', '2023-12-21', 89, 'Completed', 'B'),
(12, 12, 12, '2023-09-15', '2023-12-20', 83, 'Completed', 'B');
-- Retrieving information
-- Retrieve information from Department table
SELECT * FROM Department;
SELECT * FROM Department WHERE DepartmentID = 1;
-- Retrieve information from AdministrativeStaff table
SELECT * FROM AdministrativeStaff;
-- Retrieve information of administrative staff members in a specific department
SELECT * FROM AdministrativeStaff WHERE DepartmentID = 1;
-- END
-- Retrieve information about a particular AdministrativeStaff member
SELECT * FROM AdministrativeStaff WHERE StaffID = 103;
-- Retrieve specific information about a particular AdministrativeStaff member
SELECT FirstName, LastName, Position FROM AdministrativeStaff WHERE StaffID = 104;
SELECT * FROM Facilitator WHERE FacilitatorID = 210;
-- Retrieve information about a particular classroom
SELECT * FROM Classroom WHERE ClassroomID = 12;
-- Retrieve classrooms in the Social Commons building
SELECT * FROM Classroom WHERE BuildingName = 'Social Commons';
-- Retrieve information about a particular course
SELECT * FROM Course WHERE CourseID = 2;
-- Retrieve information about two particular students
SELECT * FROM Student WHERE StudentID IN (2, 6);
-- Retrieve information about financial aid transactions
SELECT * FROM FinancialAid;
-- Retrieve information about course enrollments
SELECT * FROM CourseEnrollment;
-- Retrieve information about course enrollments with an "in-progress" status
SELECT * FROM CourseEnrollment WHERE Status = 'In-progress';
-- Retrieve StudentID, First_Name, Last_Name, Date_Of_Birth, and Age of the students
SELECT StudentID, First_Name, Last_Name, Date_Of_Birth, Age FROM Student;
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.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
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 } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;