/
dcantuta
/
sql-project-checker
Обзор
Документация
Войти
/
dcantuta
/
sql-project-checker
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
main
examples/good_project.sql
72 строки
2 KB
worddragon
Initial SQL project checker
19 май 2026, 21:19
19 май 2026, 21:19
8758dc9
Код
Авторство
О чём код?
-- good_project.sql -- A complete example of a student SQL project -- Create tables CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ); CREATE TABLE courses ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, credits INTEGER ); CREATE TABLE enrollments ( student_id INTEGER, course_id INTEGER, grade TEXT, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) ); -- Insert test data INSERT INTO students (id, name, email) VALUES (1, 'Alice Johnson', 'alice@example.com'), (2, 'Bob Smith', 'bob@example.com'), (3, 'Carol White', 'carol@example.com'); INSERT INTO courses (id, title, credits) VALUES (101, 'Database Systems', 3), (102, 'Web Development', 3), (103, 'Data Science', 4); INSERT INTO enrollments (student_id, course_id, grade) VALUES (1, 101, 'A'), (1, 102, 'B'), (2, 101, 'C'), (3, 103, 'A'); -- Example SELECT queries SELECT * FROM students; SELECT s.name, c.title FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id; -- Function: calculate GPA CREATE FUNCTION calculate_gpa(grade TEXT) RETURNS REAL AS $$ BEGIN CASE grade WHEN 'A' THEN RETURN 4.0; WHEN 'B' THEN RETURN 3.0; WHEN 'C' THEN RETURN 2.0; WHEN 'D' THEN RETURN 1.0; ELSE RETURN 0.0; END CASE; END; $$ LANGUAGE plpgsql; -- Trigger: log enrollment changes CREATE TRIGGER log_enrollment_change AFTER INSERT OR UPDATE ON enrollments FOR EACH ROW EXECUTE FUNCTION log_enrollment_update(); -- View: student course list CREATE VIEW student_course_list AS SELECT s.name, c.title, e.grade FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id;