--DROP DATABASE IF EXISTS schoolmanagement;

CREATE DATABASE IF NOT EXISTS schoolmanagement;

USE schoolmanagement;

CREATE TABLE students(
    id INT,
    name VARCHAR(50),
    age INT,
    marks INT,
    school VARCHAR(50)
);
INSERT INTO students(id, name, age, marks, school)
VALUES
(1, 'Rahul', 15, 85, 'gcs'),
(2, 'Priya', 14, 92, 'gcs'),
(3, 'Aman', 16, 78, 'gcs');

SELECT * FROM students;

SELECT name FROM students;

SELECT name FROM students
WHERE marks>80;

SElECT * FROM students
ORDER BY marks DESC;

SELECT COUNT(*) FROM students;

UPDATE students
SET marks = 80

WHERE id = 1;

SELECT marks FROM students;

CREATE TABLE teachers(
    id INT,
    name VARCHAR(50),
    experience INT,
    subject VARCHAR(50),
    school VARCHAR(50)
);

INSERT INTO teachers(id, name, experience, subject, school);
VALUES
(1, 'Prof. Anamika', 7, 'English', 'gcs'),
(2, 'Prof. John', 12, 'Math', 'gcs'),
(3, 'Prof, Green', 5, 'Science', 'gcs');

SELECT * FROM teachers;

SELECT name, subject FROM teachers;
SELECT name, experience+2 FROM teachers;
SELECT name, marks + 5 AS updated_marks
FROM students;

SELECT * FROM teachers 
WHERE subject!='Math';

ALTER TABLE teachers
ADD age INT;

SELECT * FROM students
WHERE age>10 AND marks>80;

SELECT * FROM students
WHERE age>10 OR marks>80;

UPDATE teachers
SET age = 50
WHERE id = 1;

UPDATE teachers
SET age = 34
WHERE id = 2;

UPDATE teachers
SET age = 40
WHERE id = 3;

SELECT * FROM students
WHERE marks BETWEEN 70 AND 90;

ALTER TABLE students
ADD city VARCHAR(50);

UPDATE students
SET city = 'Delhi'
WHERE id=1;

UPDATE students
SET city = 'Mumbai'
WHERE id=2;

UPDATE students
SET city = 'Dubai'
WHERE id=3;

SELECT * FROM students
WHERE city IN ('Delhi', 'Mumbai');

-- Names starting with A
SELECT * FROM students
WHERE name LIKE 'A%';

-- Names ending with a
SELECT * FROM students
WHERE name LIKE '%a';

-- Names containing h
SELECT * FROM students
WHERE name LIKE '%h%';

--ALTER TABLE teachers
--DROP COLUMN subject;

--DELETE FROM students
--WHERE id=2;

--TRUNCATE TABLE students;
SELECT * FROM students;

ALTER TABLE students
MODIFY marks DECIMAL(5,2);

/*ALTER TABLE students
RENAME TO school_students; */

--SELECT * FROM students;

SELECT * FROM students
WHERE marks>70
ORDER BY marks ASC;

/*The GROUP BY clause in SQL is used to group rows that have the same values in a particular column.
It is mostly used with aggregate functions such as:
COUNT() – Counts the number of records.
SUM() – Calculates the total.
AVG() – Calculates the average.
MAX() – Finds the maximum value.
MIN() – Finds the minimum value.*/

SELECT city, COUNT(*)
FROM students
GROUP BY city;

SELECT city, SUM(marks)
FROM students

SELECT school FROM teachers;


/*JOIN in DBMS (MySQL)
In DBMS, JOIN is used to combine data from two or more tables based on a related column between them.
Types of JOIN in SQL
There are four commonly used JOIN types:
1. INNER JOIN
Returns only matching records from both tables.
2. LEFT JOIN
Returns all records from the left table and matching records from the right table.
3. RIGHT JOIN
Returns all records from the right table and matching records from the left table.
4. FULL OUTER JOIN
Returns all records from both tables, whether matching or not.*/

/*SELECT  school
FROM students
INNER JOIN teachers
ON students.school=teachers.school;*/