Advanced_data

Question 1

What are relational databases? Explain the theory behind them!

Answer 1

• Relational databases store structured data in tables with defined relationships between them, following the principles of the relational model.

Question 2

Why SQL is important these days?

Answer 2

• SQL is essential for managing and querying large datasets in relational databases, which are prevalent in modern applications and it is ensuring data security and integrity.

Question 3

All the new GUI tools enable me to click a button to write SQL. Why should I spend time learning to write SQL manually?

Answer 3

• Understanding SQL enables greater control, customization, and troubleshooting in complex queries beyond what GUI tools offer.

Question 4

If SQL is standardized, should you be able to program with SQL on any databases?

Answer 4

• Yes, you can generally write and execute standard SQL queries on different relational databases. However, there might be differences in syntax and supported features, which could require adjustments when working with specific databases. These variations are often related to non-standard features, data types, functions, and performance optimizations that are implemented differently by different DBMS vendors.

Question 5

What makes SQL a nonprocedural language?

Answer 5

• SQL is considered a nonprocedural language because it focuses on specifying what you want to achieve rather than how to achieve it. In other words, you describe the desired outcome, and the database management system (DBMS) figures out the most efficient way to execute your request.

Question 6

What can you do with SQL?

Answer 6

• SQL allows you to query, insert, update, and delete data, create and modify database structures, and define user access permissions.

Question 7

Do the following statements return the same or different output? Why?

SELECT * FROM CHECKS;
select * from checks;

Answer 7

• The statements return the same output, as SQL is case-insensitive for keywords but not for column/table names.

Question 8

The following queries do not work when entered into the command line psql console. Why not?

Select *
Select * from checks
Select amount name payee FROM checks;

Answer 8

• The first needs to specify the table from which we want to select all columns like line 2. The third line must have a comma between columns name.

Question 9

What shorthand could you use instead of WHERE a >= 10 AND a <=30?

Answer 9

• I can use

  WHERE a BETWEEN 10 AND 30

Question 10

Which function capitalizes the first letter of a character string and makes the rest lowercase?

Answer 10

• The INITCAP() function performs this action.

Question 10

Will this query work? Why?

SELECT COUNT(LASTNAME) FROM CHARACTERS;

Answer 10

• The query will work if there’s a table named “CHARACTERS” with a column “LASTNAME” since COUNT operates on columns.

Question 11

Assuming that they are separate columns, which function(s) would splice together FIRSTNAME and LASTNAME?

Answer 11

• The CONCAT() function can combine FIRSTNAME and LASTNAME.

Question 11

What is the function of the GROUP BY clause?

Answer 11

• The GROUP BY clause in SQL is used to group rows from a table into summary rows based on the values in one or more columns. It’s often used in combination with aggregate functions like SUM, COUNT, AVG, MAX, and MIN to perform calculations on groups of data rather than on individual rows. This is particularly useful for generating reports, performing analysis, and summarizing data.

SELECT CustomerName, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerName;

Question 11

Why are so few functions defined in the ANSI standard and so many defined by the individual implementations?

Answer 11

• Database systems often introduce proprietary functions to differentiate themselves, leading to a variety of extensions beyond the standard.

Question 12

When using the HAVING clause, do you always have to use a GROUP BY also?

Answer 12

• Yes, HAVING is used in conjunction with GROUP BY to filter grouped data.
• Use the WHERE clause for filtering individual rows based on column values, and use the HAVING clause for filtering groups of rows based on aggregate values after using GROUP BY.

Question 12

Can you use ORDER BY on a column that is not one of the columns in the SELECT statement?

Answer 12

yes, you can:
SELECT ship_address from orders
ORDER BY employee_id;

Question 13

Explain the different kind of joins! (outer, inner, left, right, natural, etc.)

Answer 13

• Inner join: Returns only the matching rows from both tables.
• Left join: Returns all rows from the left table and matching rows from the right table.
• Right join: Returns all rows from the right table and matching rows from the left table.
• Outer join: Includes unmatched rows from one or both tables.
• Natural join: Automatically matches columns with the same name, excluding duplicates.

Question 14

How many tables can you join on?

Answer 14

You can join multiple tables in a single query, but the specific limit varies based on the database system.

Question 15

Would it be fair to say that when tables are joined, they actually become one table?

Answer 15

• No, when tables are joined, they are logically combined for the duration of the query, but they remain separate physical entities.

Question 16

How many rows would a two-table join produce if one table had 50,000 rows and the other had 100,000?

Answer 16

• The number of rows produced depends on the join condition. An inner join with suitable conditions might produce fewer rows than either table, while an outer join could produce more.

Question 17

What type of join appears in the following SELECT statement?

SELECT e.name, e.employee_id, ep.salary  
FROM employee_tbl e, employee_pay_tbl ep  
WHERE e.employee_id = ep.employee_id;

Answer 17

• This statement uses an inner join based on the equality of the “employee_id” columns.

Question 18

In joining tables are you limited to one-column joins, or can you join on more than one column?

Answer 18

• You can join on multiple columns, enabling more specific matching conditions.

Question 19

Does SQL have a statement for file import/export operations?

Answer 19

Yes, SQL provides commands like COPY or LOAD for importing/exporting data to/from files.

Question 20

Can you copy data from a table into itself using the INSERT command? You would like to make duplicate copies of all the existing records and change the value of one field.

Answer 20

• Yes, you can use the INSERT INTO … SELECT statement to achieve this by selecting data from the same table and modifying the necessary field.

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department)
SELECT EmployeeID, FirstName, LastName, 'NewDepartment' AS Department
FROM Employee;

Question 21

What would happen if you issued the following statement?

DELETE * FROM COLLECTION;

Answer 21

• The statement is invalid. It should be

  DELETE FROM COLLECTION;

  without the asterisk.

Question 22

Can you remove columns with the ALTER TABLE statement?

Answer 22

• Yes, you can use the ALTER TABLE statement to remove columns, but this action may involve data loss.

 ALTER TABLE Employee
DROP COLUMN Department;

Question 23

Is the DROP TABLE command functionally equivalent to the DELETE FROM table_name command?

Answer 23

• No,

  DROP TABLE

  removes the entire table and its structure, while

  DELETE FROM

  only removes data but leaves the table structure intact.

Question 24

What is the difference between the functionality of the DELETE FROM table_name and the TRUNCATE TABLE command?

Answer 24

• DELETE FROM

  removes individual rows with the option to use a

  WHERE

  clause, while

  TRUNCATE TABLE

  removes all rows, but it’s faster and can’t be rolled back.

Question 25

When a table is created, who is the owner?

Answer 25

• The owner of a table is typically the user who executed the

  CREATE TABLE

  statement, or the user specified in the statement if it includes an OWNER clause.

Question 26

If data in a character column has varying lengths, what is the best choice for the data type?

Answer 26

• The

  VARCHAR

  data type is the best choice for character columns with varying lengths.