SQL Fundamentals and Commands Lecture

Question-and-answer flashcards covering catalogs, schemas, development vs. production environments, SQL command categories (DDL, DML, DCL), common data types, table creation, data manipulation (INSERT, SELECT, UPDATE, DELETE), and privilege control (GRANT, REVOKE).

What is the primary role of the catalog in an SQL environment?

The catalog contains descriptions of all objects that are part of the database, regardless of which user created them.

What does a schema in an SQL environment typically include?

Base tables, views, domains, constraints, character sets, triggers, and roles.

Why should a database have both development and production versions?

The development version is used for building and testing enhancements without affecting live data, while the production version captures real business data and must be tightly controlled and monitored.

Into which SQL command category does CREATE TABLE fall?

DDL (Data Definition Language).

Into which SQL command category does INSERT INTO fall?

DML (Data Manipulation Language).

Into which SQL command category does GRANT fall?

DCL (Data Control Language).

Into which SQL command category does SELECT fall?

DML (Data Manipulation Language).

Into which SQL command category does ALTER TABLE fall?

DDL (Data Definition Language).

Into which SQL command category does UPDATE fall?

DML (Data Manipulation Language).

Into which SQL command category does REVOKE fall?

DCL (Data Control Language).

Into which SQL command category does DELETE fall?

DML (Data Manipulation Language).

Which SQL data type is most appropriate for a unique identifier such as EmployeeID?

INTEGER or INT.

Which SQL data type is most appropriate for storing an employee’s full name?

VARCHAR(n) or VARCHAR2(n).

Which SQL data type is most appropriate for storing an employee’s hire date?

DATE or TIMESTAMP.

Which SQL data type is most appropriate for storing an employee’s salary?

NUMERIC(p,s) or DECIMAL(p,s).

Which SQL data type is most appropriate for storing a binary file such as an employee’s profile picture?

BLOB.

Which SQL command creates a table named Employee_T with columns EmployeeID, EmployeeName, HireDate, Salary, and ProfilePicture?

CREATE TABLE Employee_T (EmployeeID INT, EmployeeName VARCHAR(50), HireDate DATE, Salary NUMERIC(10, 2), ProfilePicture BLOB);

How do you specify EmployeeID as the primary key in the Employee_T table?

EmployeeID INT PRIMARY KEY.

What is the correct data type to allow the EmployeeName column up to 50 characters?

VARCHAR(50).

Which data type should be used for the HireDate column in Employee_T?

DATE.

How do you define the Salary column for precision 10 and scale 2?

Salary NUMERIC(10, 2).

What is the appropriate data type for ProfilePicture to store binary large objects?

BLOB.

Which SQL command is used to add a new record to the Employee_T table?

INSERT INTO.

What is the correct syntax to list columns when inserting into Employee_T?

INSERT INTO Employee_T (EmployeeID, EmployeeName, HireDate, Salary, ProfilePicture).

Write the SQL to insert EmployeeID 101, name 'John Doe', hire date '2023-01-15', salary 75000.00, and NULL profile picture into Employee_T.

INSERT INTO Employee_T (EmployeeID, EmployeeName, HireDate, Salary, ProfilePicture) VALUES (101, 'John Doe', '2023-01-15', 75000.00, NULL);

How do you represent a missing value for ProfilePicture when inserting a record?

Use NULL.

Which SQL command retrieves specific columns from a table?

SELECT.

In a SELECT statement, which clause specifies the table to retrieve data from?

FROM.

How do you write a condition to filter results where HireDate is after January 1, 2022?

WHERE HireDate > '2022-01-01'.

Which SQL command modifies existing data in a table?

UPDATE.

In an UPDATE statement, which clause sets the new value for Salary?

SET.

Which clause identifies which row to update, such as EmployeeID 101?

WHERE.

Which SQL command deletes data from a table?

DELETE FROM.

What is the correct SQL to specify the table when deleting data from Employee_T?

DELETE FROM Employee_T.

How do you specify the condition to delete the employee with EmployeeID = 101?

WHERE EmployeeID = 101.

Write the complete SQL command to delete the employee with EmployeeID 101 from Employee_T.

DELETE FROM Employee_T WHERE EmployeeID = 101;

Which SQL command grants privileges to a user on a table?

GRANT.

In a GRANT command, which clause specifies the table on which privileges are granted?

ON.

Write the SQL to grant SELECT and INSERT on Employee_T to user1.

GRANT SELECT, INSERT ON Employee_T TO user1;

Which SQL command revokes a specific privilege from a user?

REVOKE.

In a REVOKE command, which keyword specifies the table from which a privilege is revoked?

ON.

Write the SQL to revoke INSERT privilege on Employee_T from user1.

REVOKE INSERT ON Employee_T FROM user1;