SQL Coding Reviewer - Lessons 4 to 7

This set of flashcards covers SQL basics, functions, advanced queries (joins and subqueries), database structure (DDL), and constraints based on Lessons 4 through 7.

How do you retrieve all columns from a table named students?

SELECT * FROM students;

How do you display only the student's first name and age from the students table?

SELECT first_name, age FROM students;

How do you filter records to display students whose age is $18$?

SELECT * FROM students WHERE age = 18;

What SQL command retrieves students older than $18$?

SELECT * FROM students WHERE age > 18;

How do you sort data in the students table from smallest to largest age?

SELECT * FROM students ORDER BY age ASC;

How do you sort data in the students table from largest to smallest age?

SELECT * FROM students ORDER BY age DESC;

How do you display students aged $18$ or older and sort them by age in ascending order?

SELECT * FROM students WHERE age >= 18 ORDER BY age ASC;

Which SQL function converts a first_name to all capital letters?

SELECT UPPER(first_name) FROM students;

Which function converts a name to lowercase?

SELECT LOWER(first_name) FROM students;

How do you capitalize the first letter of each word in the first_name column?

SELECT INITCAP(first_name) FROM students;

How do you find the number of characters in a name column?

SELECT LENGTH(first_name) FROM students;

How do you extract $3$ characters from the first_name column starting from position $1$?

SELECT SUBSTR(first_name, 1, 3) FROM students;

How do you replace the text 'John' with 'Johnny' in the first_name column?

SELECT REPLACE(first_name, 'John', 'Johnny') FROM students;

What is the result of SELECT ROUND(15.678) FROM dual;?

$$16$$

How do you remove decimal places from $15.678$ without rounding?

SELECT TRUNC(15.678) FROM dual;

How do you find the remainder of $10$ divided by $3$ in Oracle?

SELECT MOD(10, 3) FROM dual;

How do you display the current system date in Oracle?

SELECT SYSDATE FROM dual;

How do you count the total number of records in the students table?

SELECT COUNT(*) FROM students;

Which function is used to calculate the total salary of all employees?

SELECT SUM(salary) FROM employees;

Which function finds the average, highest, and lowest salary respectively?

AVG(salary), MAX(salary), and MIN(salary)

How do you group employees by department_id and count them?

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

How do you display only departments that have more than $5$ employees?

SELECT department_id, COUNT() FROM employees GROUP BY department_id HAVING COUNT() > 5;

What is the primary difference between WHERE and HAVING?

WHERE filters rows; HAVING filters groups.

How do you join the employees table (e) and departments table (d) on department_id?

SELECT e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

How do you find employees who work in the 'IT' department using a subquery?

SELECT first_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

Which operator do you use when a subquery returns multiple values (MANY)?

IN

How do you find employees whose salary is greater than the average salary using a subquery?

SELECT first_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

How do you check if a department has employees using the EXISTS operator?

SELECT d.department_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

How do you add a column named birthday with the DATE data type to the students table?

ALTER TABLE students ADD birthday DATE;

How do you change the data type of the first_name column to VARCHAR2(50)?

ALTER TABLE students MODIFY first_name VARCHAR2(50);

How do you rename the column 'section' to 'class_section' in the students table?

ALTER TABLE students RENAME COLUMN section TO class_section;

How do you remove the birthday column from the students table?

ALTER TABLE students DROP COLUMN birthday;

How do you rename the table 'students' to 'shs_students'?

RENAME students TO shs_students;

What is the difference between DROP TABLE and TRUNCATE TABLE?

DROP TABLE deletes the entire table and its data; TRUNCATE TABLE empties the table but keeps the structure.

How do you delete a specific record where the student_id is $101$?

DELETE FROM students WHERE student_id = 101;

How do you define a column as a primary key when creating a table?

student_id NUMBER PRIMARY KEY

Which constraint prevents duplicate values in a column like email?

UNIQUE

How do you create a constraint to ensure age is at least $18$?

CHECK (age >= 18)

Which constraint is used to connect a column in one table to the primary key of another table?

FOREIGN KEY