NEW
Font size
WorksheetsQUIZ 1 BASIC SQL BCA 3rd Year
Total questions: 36
Worksheet time: 1hrs 12mins
The language used application programs to request data from the DBMS is referred to as __________
DDL
DML
Query Language
All of the mentioned
Match SQL language with SQL commands
1)DDL i)Revok, Grant
2)DML ii)rename a table
3)TCL iii)insert, update
4)DCL iv)rollback , commit
1-iii,2-i,3-ii,4-iv
1-iii,2-ii,3-iv,4-i
1-ii,2-iii,3-iv,4-i
1-i,2-iii,3-ii,4-iv
Which of the following is not a type of SQL statement?
Data Manipulation Language (DML)
Data Definition Language (DDL)
Data Control Language (DCL)
Data Communication Language (DCL)
SQL Query to delete all rows in a table without deleting the table (structure, attributes, and indexes)
DELETE FROM table_name;
DELETE TABLE table_name;
DROP TABLE table_name;
None
Correct syntax query syntax to drop a column from a table is
DELETE COLUMN column_name;
DROP COLUMN column_name;
ALTER TABLE table_name DROP COLUMN column_name;
None is correct.
Which command is used for removing a table and all its data from the database:
Create command
Drop table command
Alter table command
All of the mentioned
Which of the following language is used to specify database Schema ?
DDL
DML
DCL
None
DDL Stands for ___________.
Data Management Language
Data Manupulation Language
Data Development Language
Data Definition Language
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)
);
INSERT INTO patients(Name, Age, Gender, Diagnosis) VALUES ('Anna Cruz', 35, 'F', 'Diabetes');
INSERT INTO Patients (Name, Age, Gender, Diagnosis) VALUES ('Anna Cruz', 35, 'F', 'Diabetes');
INSERT INTO PATIENTS (Name, Age, Gender, Diagnosis) VALUES ('Anna Cruz', 35, 'F', 'Diabetes');
INSERT INTO Patient (Name, Age, Gender, Diagnosis) VALUES ('Anna Cruz', 35, 'F', 'Diabetes');
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)
);
UPDATE student SET GPA = 3.85 WHERE StudentID = 10234;
UPDATE Student SET GPA = 3.85 WHERE StudentID = 10234;
UPDATE STUDENTS SET GPA = 3.85 WHERE StudentID = 10234;
UPDATE Students SET GPA = 3.85 WHERE StudentID = 10234;
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
);
DELETE FROM cases WHERE Status = 'Closed' AND ClosingDate < '2015-01-01';
DELETE FROM Cases WHERE Status = 'Closed' AND ClosingDate < '2015-01-01';
DELETE FROM CASES WHERE Status = 'Closed' AND ClosingDate < '2015-01-01';
DELETE FROM case WHERE Status = 'Closed' AND ClosingDate < '2015-01-01';
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
);
SELECT * FROM employee WHERE Department = 'Sales' ORDER BY HireDate;
SELECT * FROM Employees WHERE Department = 'Sales' GROUP BY HireDate;
SELECT * FROM Employees WHERE Department = 'Sales' ORDER BY HireDate;
SELECT * FROM employee WHERE Department = 'Sales' GROUP BY HireDate;
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)
);
SELECT Category, AVG(Price) AS AvgPrice FROM Products GROUP BY Category;
SELECT category, AVG(price) AS AvgPrice FROM products GROUP BY Category;
SELECT Categories, AVG(Prices) AS AvgPrice FROM Product GROUP BY Categories;
SELECT CATEGORY, AVG(PRICE) AS AVGPRICE FROM PRODUCTS GROUP BY CATEGORY;
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)
);
SELECT Name FROM Students WHERE GPA < 3.0;
SELECT Name FROM Students WHERE GPA > 3.0;
SELECT Name FROM Students WHERE GPA >= 3.0;
SELECT * FROM Students WHERE GPA > 3.0;
How do we Update the diagnosis of a patient with ID 12345 to 'Hypertension' 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)
);
UPDATE Patients SET Diagnosis = 'Hypertension' WHERE PatientID = 1234;
UPDATE patients SET Diagnosis = 'Hypertension' WHERE PatientID = 12345;
UPDATE Patients SET Diagnosis = 'Hypertension' WHERE ID = 12345;
UPDATE Patients SET Diagnosis = 'Hypertension' WHERE PatientID = 12345;
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
);
SELECT COUNT(*) FROM Cases;
SELECT COUNT(*) FROM Cases WHERE Status = 'Open';
SELECT COUNT(*) FROM Cases WHERE Status != 'Closed';
SELECT COUNT(CaseID) FROM Cases WHERE Status = 'Open';
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)
);
SELECT * FROM Patients WHERE Diagnosis LIKE 'Diabetes';
SELECT Name, Age FROM Patients WHERE Diagnosis = 'Diabetes';
SELECT * FROM Patients WHERE Diagnosis = 'Diabetes' ORDER BY Name;
SELECT * FROM Patients WHERE Diagnosis = 'Diabetes';
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
);
SELECT COUNT(*) FROM Courses WHERE CourseName = 'Mathematics';
SELECT EnrolledStudents FROM Courses WHERE CourseName = 'Mathematics';
SELECT COUNT(*) FROM Courses WHERE EnrolledStudents > 0;
SELECT COUNT(EnrolledStudents) FROM Courses WHERE CourseName = 'Mathematics';
How do we Insert a new product into the Products table with name, category, and price 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)
);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Laptop', 'Electronics', 999.99);
INSERT INTO Products (ProductName, Category) VALUES ('Laptop', 'Electronics');
INSERT INTO products (ProductName, Category, Price) VALUES ('Laptop', 'Electronics', 999.99);
INSERT INTO Products (Name, Category, Price) VALUES ('Laptop', 'Electronics', 999.99);
How do we Retrieve the names of all employees with a salary greater than 50000 using SQL? refer to the table structure below
CREATE DATABASE CompanyDB;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2)
);
SELECT Name FROM Employees WHERE Salary < 50000;
SELECT Name FROM Employees WHERE Salary > 50000;
SELECT Name FROM Employees WHERE Salary >= 50000;
SELECT * FROM Employees WHERE Salary > 50000;
How do we Retrieve the details of all students who are enrolled in 'Computer Science' using SQL? refer to the table structure below
CREATE DATABASE UniversityRecords;
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseName VARCHAR(100)
);
SELECT * FROM Enrollments WHERE CourseName = 'Computer Science';
SELECT StudentID FROM Enrollments WHERE CourseName = 'Computer Science';
SELECT * FROM Enrollments WHERE CourseName LIKE 'Computer Science';
SELECT * FROM Enrollments WHERE CourseName = 'Computer Science' ORDER BY StudentID;
How do we Update the salary of an employee with ID 5678 to 60000 using SQL? refer to the table structure below
CREATE DATABASE CompanyDB;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2)
);
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 5678;
UPDATE employees SET Salary = 60000 WHERE EmployeeID = 5678;
UPDATE Employees SET Salary = 60000 WHERE ID = 5678;
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 56789;
How do we Retrieve the details of all courses that have more than 30 enrolled students using SQL? refer to the table structure below
CREATE DATABASE UniversityRecords;
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
EnrolledStudents INT
);
SELECT * FROM Courses WHERE EnrolledStudents > 30;
SELECT * FROM Courses WHERE EnrolledStudents >= 30;
SELECT CourseID, CourseName FROM Courses WHERE EnrolledStudents > 30;
SELECT CourseName FROM Courses WHERE EnrolledStudents > 30;
How do we Update the name of a product with ID 101 to 'Smartphone' 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)
);
UPDATE Products SET ProductName = 'Smartphone' WHERE ProductID = 101;
UPDATE products SET ProductName = 'Smartphone' WHERE ProductID = 101;
UPDATE Products SET ProductName = 'Smartphone' WHERE ID = 101;
UPDATE Products SET Name = 'Smartphone' WHERE ProductID = 101;
How do we Retrieve the details of all employees in the 'HR' department using SQL? refer to the table structure below
CREATE DATABASE CompanyDB;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(100),
HireDate DATE
);
SELECT * FROM Employees WHERE Department = 'HR' ORDER BY HireDate;
SELECT Name FROM Employees WHERE Department = 'HR';
SELECT * FROM Employees WHERE Department = 'HR';
SELECT * FROM Employees WHERE Department LIKE 'HR';
How do we Delete a product with ID 202 from the Products table 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)
);
DELETE FROM Products WHERE ProductID = 202;
DELETE FROM products WHERE ProductID = 202;
DELETE FROM Products WHERE ID = 202;
DELETE FROM Products WHERE ProductID = 2020;
How do we Update the age of a patient with ID 6789 to 40 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)
);
UPDATE Patients SET Age = 40 WHERE PatientID = 6789;
UPDATE patients SET Age = 40 WHERE PatientID = 6789;
UPDATE Patients SET Age = 40 WHERE ID = 6789;
UPDATE Patients SET Age = 40 WHERE PatientID = 67890;
How do we Retrieve the names of all students enrolled in 'Mathematics' using SQL? refer to the table structure below
CREATE DATABASE UniversityRecords;
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseName VARCHAR(100)
);
SELECT StudentID, CourseName FROM Enrollments WHERE CourseName = 'Mathematics';
SELECT * FROM Enrollments WHERE CourseName = 'Mathematics';
SELECT * FROM Enrollments WHERE CourseName LIKE 'Mathematics';
SELECT StudentID FROM Enrollments WHERE CourseName = 'Mathematics';
Which of the following are the five built-in functions provided by SQL?
A. COUNT, SUM, AVG, MAX, MIN
B. SUM, AVG, MIN, MAX, MULT
C. SUM, AVG, MULT, DIV, MIN
D. SUM, AVG, MIN, MAX, NAME
The HAVING clause does which of the following?
A. Acts like a WHERE clause but is used for groups rather than rows.
B. Acts like a WHERE clause but is used for rows rather than columns.
C. Acts like a WHERE clause but is used for columns rather than groups.
D. Acts EXACTLY like a WHERE clause
Which one of the following sorts rows in SQL?
A.SORT BY
B.ALIGN BY
C.ORDER BY
D.GROUP BY
Which of the following is a SQL aggregate function?
A) LEFT
B) AVG
C) JOIN
D) LEN
Which of the following must be enclosed in double quotes?
A) Dates
B) Column Alias
C) Strings
D) All of the above
Find the temperature in increasing order of all cities
A) SELECT city FROM weather ORDER BY temperature;
B) SELECT city, temperature FROM weather;
C) SELECT city, temperature FROM weather ORDER BY temperature;
D) SELECT city, temperature FROM weather ORDER BY city;
Aggregate functions are functions that take a ___________ as input and return a single value.
a) Collection of values
b) Single value
c) Double value
d) All of the mentioned
Which SQL keyword is used to retrieve only unique values from a column?
DISTINCT
UNIQUE
LIMIT
GROUP BY
