Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

CREATE TABLE Student ( StudentID NUMBER(10) PRIMARY KEY ,YearStart NUMBER(4) NOT NULL…

  1. User

    CREATE TABLE Student ( StudentID NUMBER(10) PRIMARY KEY ,YearStart NUMBER(4) NOT NULL ,YearEnd NUMBER(4) );

    CREATE TABLE Teacher ( TeacherID NUMBER(10) PRIMARY KEY ,IsCurrent NUMBER(1) NOT NULL CONSTRAINT check_IsCurrent CHECK ( IsCurrent BETWEEN 0 AND 1 ) DISABLE );

    CREATE TABLE Person ( PersonID NUMBER(10) PRIMARY KEY ,FirstName VARCHAR2(255) NOT NULL ,LastName VARCHAR2(255) NOT NULL ,Gender CHAR(1) NOT NULL ,DateOfBirth DATE );

    CREATE TABLE Class ( ClassID NUMBER(10) PRIMARY KEY ,TeacherID NUMBER(10) NOT NULL ,ClassName VARCHAR2(255) NOT NULL ,YearOffered NUMBER(4) NOT NULL ,TermOffered NUMBER(1) NOT NULL CONSTRAINT check_TermOffered CHECK ( TermOffered BETWEEN 1 AND 2 ) DISABLE ,CONSTRAINT FK_Class_teacherID FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID) );

    CREATE TABLE Room ( RoomID NUMBER(10) PRIMARY KEY ,RoomNum NUMBER(4) NOT NULL ,Floor NUMBER(2) NOT NULL ,Building VARCHAR2(64) NOT NULL );

    CREATE TABLE ClassStudent ( ClassID NUMBER(10) NOT NULL ,StudentID NUMBER(10) NOT NULL ,FinalGrade NUMBER(1) CONSTRAINT check_FinalGrade CHECK ( FinalGrade BETWEEN 2 AND 6 ) DISABLE ,CONSTRAINT PK_ClassStudent PRIMARY KEY ( ClassID ,StudentID ) ,CONSTRAINT FK_ClassStudent_classID FOREIGN KEY (ClassID) REFERENCES Class(ClassID) ,CONSTRAINT FK_ClassStudent_studentID FOREIGN KEY (StudentID) REFERENCES Student(StudentID) );

    CREATE TABLE Schedule ( ClassID NUMBER(10) NOT NULL ,RoomID NUMBER(10) NOT NULL ,FromTS VARCHAR2(16) NOT NULL ,ToTS VARCHAR2(16) NOT NULL ,CONSTRAINT PK_Schedule PRIMARY KEY ( ClassID ,RoomID ,FromTS ) ,CONSTRAINT FK_Schedule_classID FOREIGN KEY (ClassID) REFERENCES Class(ClassID) ,CONSTRAINT FK_Schedule_roomID FOREIGN KEY (RoomID) REFERENCES Room(RoomID) ); Изведете списък на всички дисциплини (пълната информация за дисциплините), изучавани през 2014 г., съдържащи в името си думите “Data” или “Information” . Подредете ги по азбучен ред на името на дисциплината. SQL

  2. Assistant

    SELECT * FROM Class WHERE YearOffered = 2014 AND (ClassName LIKE '%Data%' OR ClassName LIKE '%Information%') ORDER BY ClassName ASC;

  3. User

    make it simple

  4. Assistant

    Retrieve a list of classes offered in 2014 with names containing "Data" or "Information", sorted alphabetically by class name.

    SQL:

    SELECT * FROM Class WHERE YearOffered = 2014 AND (ClassName LIKE '%Data%' OR ClassName LIKE '%Information%') ORDER BY ClassName ASC;

  5. User

    За всяка една дисциплина да се изведат кода и името на дисциплината, името на преподавателя и броя студенти, които я изучават, сортирани в нарастващ ред на кода.CREATE TABLE Student ( StudentID NUMBER(10) PRIMARY KEY ,YearStart NUMBER(4) NOT NULL ,YearEnd NUMBER(4) );

    CREATE TABLE Teacher ( TeacherID NUMBER(10) PRIMARY KEY ,IsCurrent NUMBER(1) NOT NULL CONSTRAINT check_IsCurrent CHECK ( IsCurrent BETWEEN 0 AND 1 ) DISABLE );

    CREATE TABLE Person ( PersonID NUMBER(10) PRIMARY KEY ,FirstName VARCHAR2(255) NOT NULL ,LastName VARCHAR2(255) NOT NULL ,Gender CHAR(1) NOT NULL ,DateOfBirth DATE );

    CREATE TABLE Class ( ClassID NUMBER(10) PRIMARY KEY ,TeacherID NUMBER(10) NOT NULL ,ClassName VARCHAR2(255) NOT NULL ,YearOffered NUMBER(4) NOT NULL ,TermOffered NUMBER(1) NOT NULL CONSTRAINT check_TermOffered CHECK ( TermOffered BETWEEN 1 AND 2 ) DISABLE ,CONSTRAINT FK_Class_teacherID FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID) );

    CREATE TABLE Room ( RoomID NUMBER(10) PRIMARY KEY ,RoomNum NUMBER(4) NOT NULL ,Floor NUMBER(2) NOT NULL ,Building VARCHAR2(64) NOT NULL );

    CREATE TABLE ClassStudent ( ClassID NUMBER(10) NOT NULL ,StudentID NUMBER(10) NOT NULL ,FinalGrade NUMBER(1) CONSTRAINT check_FinalGrade CHECK ( FinalGrade BETWEEN 2 AND 6 ) DISABLE ,CONSTRAINT PK_ClassStudent PRIMARY KEY ( ClassID ,StudentID ) ,CONSTRAINT FK_ClassStudent_classID FOREIGN KEY (ClassID) REFERENCES Class(ClassID) ,CONSTRAINT FK_ClassStudent_studentID FOREIGN KEY (StudentID) REFERENCES Student(StudentID) );

    CREATE TABLE Schedule ( ClassID NUMBER(10) NOT NULL ,RoomID NUMBER(10) NOT NULL ,FromTS VARCHAR2(16) NOT NULL ,ToTS VARCHAR2(16) NOT NULL ,CONSTRAINT PK_Schedule PRIMARY KEY ( ClassID ,RoomID ,FromTS ) ,CONSTRAINT FK_Schedule_classID FOREIGN KEY (ClassID) REFERENCES Class(ClassID) ,CONSTRAINT FK_Schedule_roomID FOREIGN KEY (RoomID) REFERENCES Room(RoomID) );

  6. Assistant

    SQL заявка:

    SELECT Class.ClassID, Class.ClassName, Person.FirstName || ' ' || Person.LastName AS TeacherName, COUNT(ClassStudent.StudentID) AS NumOfStudents FROM Class JOIN Teacher ON Class.TeacherID = Teacher.TeacherID JOIN Person ON Teacher.TeacherID = Person.PersonID JOIN ClassStudent ON Class.ClassID = ClassStudent.ClassID GROUP BY Class.ClassID, Class.ClassName, Person.FirstName, Person.LastName ORDER BY Class.ClassID ASC;