SQL CODE

SQL CODE - student project

CREATE TABLE myStudents (
id INT,
name VARCHAR(100),
age INT CHECK(age > 0)
);
CREATE TABLE myCourses (
id INT PRIMARY KEY,
name VARCHAR(100),
category VARCHAR(100),
numberOfStudents INT CHECK(numberOfStudents >= 0)
);
CREATE TABLE bestselingCourses (
id INT PRIMARY KEY,
name VARCHAR(100),
category VARCHAR(100),
numberOfStudents INT CHECK(numberOfStudents >= 0)
);
CREATE TABLE enrolled (
student INT,
course INT,
PRIMARY KEY (student, course)
);

CREATE TABLE courseDetail (
id INT PRIMARY KEY,
price INT
);

INSERT INTO enrolled VALUES
(1,1),
(1,2),
(2,3),
(2,1),
(3,1),
(1,4);

INSERT INTO myStudents VALUES
(1, 'Petr', 20),
(2, 'Ava', 23);

INSERT INTO myCourses (name, numberOfStudents, id, category) VALUES
('SQL', 10000, 1, 'Development'),
('Java', 5000, 2, 'Programming'),
('C', 10000, 3, 'Programming');

INSERT INTO bestselingCourses (name, numberOfStudents, id, category) VALUES
('SQL', 10000, 1, 'Development'),
('C', 10000, 3, 'Programming');

INSERT INTO courseDetail VALUES
(1, 999),
(2, 799),
(3, 899);

SELECT c.name, c.numberOfStudents, d.price FROM myCourses as c
INNER JOIN courseDetail as d ON c.id = d.id
ORDER BY c.numberOfStudents DESC, d.price DESC;

SELECT name FROM myCourses WHERE category = 'Programming';
SELECT name FROM myCourses WHERE numberOfStudents > 7500;
SELECT name, numberOfStudents FROM myCourses
WHERE numberOfStudents > 7000
AND category LIKE 'P%';
SELECT name FROM myStudents WHERE id > 1 AND age > 20;
SELECT name FROM myStudents WHERE id > 1 OR age >= 20;

SELECT course.name FROM myCourses as java, myCourses as course
WHERE java.name = 'Java'
AND java.category = course.category;

Lukas Vyhnalek
Microsoft Employee, Programming Teacher
Teacher