Back to notes
What are some common SQL data types mentioned in the lecture?
Press to flip
Common data types include NUMBER, VARCHAR2, and DATE.
What are the main steps required to install Oracle 21c?
Download the software, extract it, install the database, configure SQL Developer, and import the HR Schema.
What is the purpose of constraints in SQL and name a few examples?
Constraints enforce data integrity in a database; examples include primary keys, foreign keys, and unique constraints.
Describe the process of manually importing the HR schema into Oracle 21c.
Use SQL commands provided in the lecture to manually import the HR schema as it is not available by default in 21c.
Explain the significance of joins in SQL and provide an example use case.
Joins combine rows from two or more tables based on a related column; for instance, joining employee and department tables to retrieve employee information alongside department names.
What is the main difference between the Oracle Enterprise, Standard, and Express editions?
Enterprise is for commercial use with full features, Standard is for commercial environments with fewer features, and Express is a lightweight academic version with full features.
How can you find employees with salaries greater than the average using subqueries?
Use a subquery to calculate the average salary, then compare individual salaries against this average in the outer query to select higher earners.
What is a single-row subquery and how does it differ from a multi-row subquery?
A single-row subquery returns one row of results, while a multi-row subquery can return multiple rows.
Explain the role of SQL Developer in Oracle Database management.
SQL Developer is a GUI tool used to query, create, modify, and manage database objects in Oracle databases.
How does the INTERSECT set operator differ from the MINUS operator in SQL?
INTERSECT returns only records present in both query results, whereas MINUS returns records from the first result that are not in the second.
Identify and explain four aggregate functions in SQL.
SUM() calculates the total of a numeric column, COUNT() counts the number of rows, AVG() calculates the average value, and MAX()/MIN() return the maximum and minimum value.
Provide an example of a correlated subquery and explain how it functions.
A correlated subquery depends on data from the outer query for its results; it typically runs row by row, for example, finding employees whose salary is higher than the department average calculated externally.
What are the key differences between UNION and UNION ALL set operators?
UNION combines and removes duplicate results from two queries, while UNION ALL includes duplicates.
How do LEAD and LAG functions operate in SQL?
The LEAD function accesses data in the following rows, while the LAG function accesses data in preceding rows relative to the current row.
Differentiate between the `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()` SQL functions.
`RANK()` assigns a rank to each row, `DENSE_RANK()` assigns ranks without gaps, and `ROW_NUMBER()` assigns a unique number to each row.
Previous
Next