Unit 10 : SQL ( Part 2 )

Question 1

What keyword do we need to add to make changes to table structure?

Answer 1

1. ALTER
   
   • Add
   • Modify
   • Drop

• It can be used to change data type

Question 2

Some RDBMS ( Oracle ) do not permit changes to data types unless what?

Answer 2

1. Column to be changed is empty

Question 3

What actions does some RDBMS impose restrictions on the deletion of an attribute?

Answer 3

1. Use ALTER to drop down

Question 4

List out the useful functions that SQL provides

Answer 4

1. Count
2. Find minimum and maximun values
3. Calculates averages

Question 5

List out the aggregate functions

Answer 5

1. Count
2. Min
3. Max
4. Sum
5. Avg

Question 6

What is a view?

Answer 6

1. A virtual table based on SELECT query

Question 7

What can a view contain?

Answer 7

1. Columns
2. Computed Columns
3. Aliases
4. Aggregate Functions

Question 8

How can we create a view?

Answer 8

1. Using CREATE VIEW to generate view

Question 9

What is the most important disrinction between relational database and other databases?

Answer 9

1. Ability to combine ( join ) tables on common attributes ( primary keys )

Question 10

When is JOIN performed?

Answer 10

1. When data are retrieved from more than one table at a time

Question 11

What does JOIN is generally composed?

Answer 11

1. Equality comparison between foreign key and primary key of related tables

Question 12

What can alias be used to?

Answer 12

1. Identify source source table

• Any legal table name can be used as alias

Question 13

How to join tables with alias?

Answer 13

1. Use the FROM clause
   
   • FROM tablename alias

Question 14

What is aggregate functions?

Answer 14

1. Special functions that perform arithmetic computations over a set of rows

Question 15

What does the ORDER BY clause used to?

Answer 15

1. Sort output of SELECT statement
2. Can sort by one or more columns and use either an ascending or descending order

Question 16

When does Right Outer Join and Left Outer Join is used?

Answer 16

1. Used to select rows that have no matching values in other related table

Question 17

How does Inner Join joins both tables?

Answer 17

1. Returns rows where there is a match in both tables

Question 18

How does Left Join joins both tables?

Answer 18

1. Returns all rows from the left table along with the matching rows from the right table

• If records from left table doesn’t appear from right table, it will show NULL values
• LEFT JOIN Orders

Question 19

How does Right Join joins both tables?

Answer 19

1. Returns all rows from the right table along with the matching rows from the left table

• If records from right table doesn’t appear from left table, it will show NULL values

Question 20

What JOINS does FULL JOIN combines? ( 2 )

Answer 20

1. LEFT JOIN
2. RIGHT JOIN

Question 21

How does Full Join ( Full Outer Join ) joins both tables?

Answer 21

1. Returns all rows when there is a match in either the left or right table, if no match is found, NULL values are returned