ADVANCED DATABASE SYSTEMS EXAM

Which SQL command is used to update a student's email address in the Students table?

• UPDATE Students SET Email = '[e-mail]' WHERE StudentID = 1;

• SELECT Email FROM Students WHERE StudentID = 1;

• ALTER Students SET Email = '[e-mail]' WHERE StudentID = 1;

• INSERT INTO Students (Email) VALUES ('[e-mail]');

You want to create a table for courses offered in the current semester. Which is correct?

• CREATE TABLE Courses (CourseID INT, CourseName VARCHAR(100), Credits INT);

• CREATE TABLE Courses (CourseID INT, CourseName VARCHAR(100), Credits INT(5,2));

• CREATE TABLE Courses (CourseID, CourseName, Credits);

• CREATE TABLE Courses (CourseID INT, CourseName TEXT(100), Credits INT);

To ensure each enrolled student is a valid student, which constraint is appropriate?

• CHECK

• PRIMARY KEY

• FOREIGN KEY

• NOT NULL

You want to count students per course and display those with more than 10. What query should you use?

• SELECT CourseID, COUNT(StudentID) FROM Enrollments GROUP BY CourseID HAVING COUNT(StudentID) > 10;

• SELECT COUNT(StudentID), CourseID FROM Enrollments GROUP HAVING COUNT > 10;

• SELECT CourseID, COUNT(StudentID) FROM Enrollments WHERE COUNT(StudentID) > 10;

• SELECT CourseID FROM Enrollments GROUP BY CourseID WHERE COUNT(StudentID) > 10;

Which function would format the EnrollmentDate as "April 05, 2023"?

• FORMAT(EnrollmentDate, 'MMMM dd, yyyy')

• FORMAT(EnrollmentDate, 'mmmm dd, yyyy')

• FORMAT(EnrollmentDate, 'yyyy/MM/dd')

• FORMAT(EnrollmentDate, ' MMMM dddd, yyyy')

How do you find students enrolled in the last 30 days?

• SELECT * FROM Students WHERE EnrollmentDate <= DATEADD(DAY, -30, GETDATE());

• SELECT * FROM Students WHERE EnrollmentDate >= DATEADD(DAY, -30, GETDATE());

• SELECT * FROM Students WHERE EnrollmentDate = GETDATE();

• SELECT * FROM Students WHERE EnrollmentDate < GETDATE();

A database administrator needs to update the status of one specific student record. However, the UPDATE statement is written without a WHERE clause. What is the most likely outcome of executing this command?

• No rows in the table will be affected

• An error will occur because a condition is required

• All records in the Students table will have their values updated

• Only records with NULL values in the updated column will be changed

How would you delete enrollments before Jan 1, 2024?

• DELETE * FROM Enrollments WHERE EnrollmentDate < '2024-01-01';

• DELETE Enrollments WHERE EnrollmentDate < '2024-01-01';

• DELETE FROM Enrollments WHERE EnrollmentDate < '2024-01-01';

• REMOVE FROM Enrollments WHERE EnrollmentDate < '2024-01-01';

To restrict the Grade column to values between 0 and 100, use:

• UNIQUE

• FOREIGN KEY

• CHECK

• DEFAULT

Which query displays course titles with their credit units in the format "Title (3 units)"?

• SELECT CONCAT(CourseName, ' units: ', Credits) FROM Courses;

• SELECT CONCAT(CourseName, ' (', Credits, ' units)') FROM Courses;

• SELECT CourseName + Credits + ' units' FROM Courses;

• SELECT CONCAT('Course: ', Credits, CourseName) FROM Courses;