SQL query flashcard

Front

How do we Insert a new patient record with name, age, gender, and diagnosis having the values ('Anna Cruz', 35, 'F', 'Diabetes') using SQL? refer to the table structure below

CREATE DATABASE HospitalDB;

CREATE TABLE Patients (

    PatientID INT PRIMARY KEY AUTO_INCREMENT,

    Name VARCHAR(100),

    Age INT,

    Gender CHAR(1),

    Diagnosis VARCHAR(255)

);

Back

INSERT INTO Patients (Name, Age, Gender, Diagnosis) VALUES ('Anna Cruz', 35, 'F', 'Diabetes');

Front

How do we update the GPA of a student whose ID is 10234 using SQL? Refer to the table structure below:
CREATE DATABASE UniversityRecords;
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    GPA DECIMAL(3,2)
);

Back

UPDATE Students SET GPA = 3.85 WHERE StudentID = 10234;

Front

How do we Delete a closed case older than 10 years using SQL? Refer to the table structure below:

CREATE DATABASE LegalCases;

CREATE TABLE Cases (

    CaseID INT PRIMARY KEY,

    Status VARCHAR(50),

    ClosingDate DATE

);

Back

DELETE FROM Cases WHERE Status = 'Closed' AND ClosingDate < '2015-01-01';

Front

How do we select all employees from the Sales department and sort them by hire date using SQL? Refer to the table structure below:
CREATE DATABASE BusinessDB;
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(100),
    HireDate DATE
);

Back

SELECT * FROM Employees WHERE Department = 'Sales' ORDER BY HireDate;

Front

How do we Group products by category and show the average price per category using SQL? Refer to the table structure below:

CREATE DATABASE RetailInventory;

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Category VARCHAR(100),

    Price DECIMAL(10,2)

);

Back

SELECT Category, AVG(Price) AS AvgPrice FROM Products GROUP BY Category;

Front

How do we retrieve the names of all students with a GPA greater than 3.0 using SQL? Refer to the table structure below:

CREATE DATABASE UniversityRecords;

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(100),

    GPA DECIMAL(3,2)

);

Back

SELECT Name FROM Students WHERE GPA > 3.0;

Front

How do we update the diagnosis of a patient with ID 12345 to 'Hypertension' using SQL? Refer to the table structure provided.

Back

UPDATE Patients SET Diagnosis = 'Hypertension' WHERE PatientID = 12345;

Front

How do we Count the number of cases that are still open using SQL? Refer to the table structure below:

CREATE DATABASE LegalCases;

CREATE TABLE Cases (

    CaseID INT PRIMARY KEY,

    Status VARCHAR(50),

    ClosingDate DATE

);

Back

SELECT COUNT(*) FROM Cases WHERE Status = 'Open';

Front

How do we retrieve the details of all patients who have a diagnosis of 'Diabetes' using SQL? Refer to the table structure below:

CREATE DATABASE HospitalDB;

CREATE TABLE Patients (

    PatientID INT PRIMARY KEY AUTO_INCREMENT,

    Name VARCHAR(100),

    Age INT,

    Gender CHAR(1),

    Diagnosis VARCHAR(255)

);

Back

SELECT * FROM Patients WHERE Diagnosis = 'Diabetes';

Front

How do we count the number of students enrolled in a specific course using SQL? Refer to the table structure below:
CREATE DATABASE UniversityRecords;
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    EnrolledStudents INT
);

Back

SELECT COUNT(*) FROM Courses WHERE CourseName = 'Mathematics';