CREATE TABLE IF NOT EXISTS Student_Personal_Information (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
address VARCHAR(100),
phone_number VARCHAR(15),
email VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS Courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
course_code VARCHAR(10) UNIQUE,
professor VARCHAR(50),
start_date DATE,
end_date DATE
);
CREATE TABLE IF NOT EXISTS Grade (
grade_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
grade VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES Student_Personal_Information(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
INSERT INTO Student_Personal_Information (first_name, last_name, date_of_birth, address, phone_number, email)
VALUES
('Maria', 'Gonzales', '1998-03-15', '123 Main St, Manggahan, Pasig City', '09123456789', '[email protected]'),
('Jose', 'Rodriguez', '1997-05-22', '456 Elm St, Maybunga, Pasig City', '09234567890', '[email protected]'),
('Luz', 'Fernandez', '1999-11-10', '789 Oak St, Pinagbuhatan, Pasig City', '09345678901', '[email protected]'),
('Ramon', 'Ortiz', '1996-08-03', '234 Birch St, Kapasigan, Pasig City', '09456789012', '[email protected]'),
('Carmen', 'Santiago', '2000-02-28', '567 Cedar St, Bangbang, Pasig City', '09567890123', '[email protected]');
INSERT INTO Courses (course_name, course_code, professor, start_date, end_date)
VALUES
('Advanced Database Management System', 'ADMS101', 'Dr. Juan Santos', '2023-01-10', '2023-05-10'),
('Advanced System Analysis and Design', 'ASAD201', 'Prof. Cristina Lopez', '2023-02-15', '2023-06-15'),
('Management of Information System', 'MIS301', 'Dr. Manuel Reyes', '2023-03-20', '2023-07-20'),
('Introduction to Computing', 'COMP101', 'Prof. Lourdes Garcia', '2023-01-15', '2023-05-15'),
('Fundamentals of Programming', 'PROG101', 'Dr. Andres Torres', '2023-01-20', '2023-05-20');
INSERT INTO Grade (student_id, course_id, grade)
VALUES
(1, 1, '1.25'),
(2, 1, '1.50'),
(3, 2, '1.00'),
(4, 3, '1.50'),
(5, 4, '1.25'),
(1, 5, '1.50'),
(2, 5, '1.75'),
(3, 4, '1.00'),
(4, 3, '1.25'),
(5, 2, '1.50');
SELECT * FROM Student_Personal_Information;
SELECT * FROM Courses;
SELECT * FROM Grade;-- Add in existing table
ALTER TABLE table_name
ADD COLUMN is_active BOOLEAN DEFAULT 1
-- Add while creating the table
CREATE TABLE table_name (
is_active BOOLEAN NOT NULL default 1
);ALTER TABLE users
MODIFY COLUMN username varchar(255);desc table_name;-- First method
DESCRIBE table_name;
-- Second method
SHOW COLUMNS FROM table_name;
-- Third method
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
-- Get column names in a single line (comma seperated)
SELECT group_concat(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';--using OR operator
Select
employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
ON employee.Id = employee_address.employee_id
OR employee.pincode = employee_address.street_code
OR employee.parmanent_code = employee_address.location_code
--using AND operator
Select
employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
ON employee.Id = employee_address.employee_id
AND employee.pincode = employee_address.street_code
AND employee.parmanent_code = employee_address.location_codeSELECT
(SELECT DISTINCT
column_name
FROM
table_name
ORDER BY column_name DESC
LIMIT 1 OFFSET 1
) AS second_highest_value
;SELECT Id, FirstName, Lastname
FROM Students
ORDER BY STR_TO_DATE(CreatedDate, '%d-%m-%Y') ASC;
-- USE 'DESC' in place of 'ASC' if you want result in decending formatALTER TABLE Students
DROP COLUMN Email;-- USE -- FOR SINGLE LINE COMMENT
-- SELECT * FROM STUDENTS
/* BLOCK LEVEL COMMENTS - COMMENT OUT A FULL BLOCK */
/*
SELECT NAME
FROM USERS
*/mysql -u root -p test_db
# test_dbã¯ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å-- Remove single table
DROP TABLE table_name;
-- Remove multiple tables
DROP TABLE table_name_1, table_name_2;-- SINGLE TABLE
RENAME TABLE courses TO subjects;
-- MULTIPLE TABLES
RENAME TABLE
courses TO subjects,
topics TO subject_topics;-- While creating table (Single)
CREATE TABLE table_name( colum_1 data_type, column_2 data_type UNIQUE );
-- While creating table (Combine multiple column)
CREATE TABLE table_name(
column_1 data_type,
column_2 data_type,
UNIQUE(column_1, column_2)
);
-- Add unique constraints on existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column_name);SHOW TABLES:SHOW DATABASES;-- To create database
CREATE DATABASE db_name;
--To select database
USE db_name;-- Match with list values
SELECT
first_name,
last_name,
city
FROM
Students
WHERE
city IN ('Delhi' , 'Newyork');
--Match with subquery records
SELECT
first_name,
last_name,
city
FROM
Students
WHERE city IN
(
SELECT
city
FROM
city_list
);SELECT DISTINCT
column_name1, column_name2
FROM
table_name;SELECT * FROM table_name FROM ORDER BY column_name ASC
SELECT * FROM table_name FROM ORDER BY column_name DESCALTER TABLE
ContactDetails
ADD
EmployeeId INT UNSIGNED NOT NULL;
ALTER TABLE
ContactDetails
ADD
CONSTRAINT fk_EMP_CONTACT FOREIGN KEY (EmployeeId) REFERENCES Employee(Id);SELECT
column_name,
COUNT(column_name)
FROM
table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;//select all rows from table
SELECT * FROM table_name
//select rows based on a condition
SELECT * FROM table_name WHERE column_name='value'ALTER TABLE
table_name
ADD (
Column_name1 data_type,
Column_name2 data_type (size)
);INSERT INTO table_name (Column1, Column2, Column3)
VALUES(1, 'Value2', 'Value3')
or
INSERT INTO table_name
SET
Column1 = 'Value1',
Column2 = 'Value2',
Column3 = 'Value3'CREATE TABLE table_name(
Id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
Column1 char(40) DEFAULT NULL,
Column2 varchar(100) DEFAULT '',
Date timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(Id),
UNIQUE (Column1)
)