mode 3 sql functionality

Question 1

What is a natural key?

Answer 1

it is a key that was obtained from outside of the

• database and is already unique
• > example: pokemon, SSN, license plate, username…

Question 2

What is a Surrogate Key?

Answer 2

It is a key that was generated inside of the database itself.

Question 3

What is a serial?

Answer 3

SERIAL is an integer

SERIAL keyword creates a new id in the table

Question 4

What is a Composite Key?

Answer 4

two or more fields/ columns that create a unique value.

Question 5

What is DQL?

Answer 5

Data Query Language

* SELECT statements is a part of DQL.

Question 6

What is an alias?

Answer 6

its an alternate name,

alias allows you to give fields and tables another name the record set.

Question 7

what is a record set?

Answer 7

It is the temporary table that you get back from a query

Question 8

Different ways to use selects

Answer 8

SELECT * FROM employee;
–WHERE clause is a filter
SELECT * FROM employee WHERE title=’Sales Support Agent’;
– AND keyword only queries records if both conditions are met
SELECT * FROM employee WHERE title = ‘Sales Support Agent’ AND first_name = ‘Steve’;
– OR keyword queies records where only one of the conditions are met
SELECT * FROM employee WHERE first_name = ‘Andrew’ OR first_name = ‘Steve’;
–ALSO you can use >, =
SELECT * FROM employee WHERE reports_to >= (0+1);

Question 9

How we use not equals on select

Answer 9

--is there not equals? 
/*
 * Ways to do not equals
 *  - !=
 *  - <>
 *  - NOT
 * */

Question 10

How to do more generalized filters with strings?

Answer 10

• • LIKE keyword, finds patterns and ATTRIBUTES
• —– ‘%’ is a wildcard that allows 0 or more characters
• —– ‘_’ is a wildcard that allows one specific character

Question 11

How do we sort the data?

——

Answer 11

the keyword ORDER BY

Question 12

Aggregate Functions

What is an aggregate function?

Answer 12

A calculation operating on a group of recrods/entriess, often producing 1 result;

Question 13

Different types of aggregate functions

Answer 13

There are many different types of aggregate functions

• Examples:
• • AVG: returns average on records
• • MIN: minimum value
• • MAX: max value
• • SUM: sum of elements in the column
• • DISTINCT:

Question 14

What is the order of the WHERE, GROUP BY & HAVING keywords

Answer 14

order of the keywords matter, “WHERE” goes before “GROUP BY”

• goes before “HAVING”
• */

Question 15

What is an aggregate function?

Answer 15

calculation on a set records to get generally a single value

Question 16

What is a scalar function?

Answer 16

function that works on individual records

Question 17

Types of Scalar functions

Answer 17

Numeric:
ABS(-99); FLOOR(88.77); CEIL(88.77);TRUNC(23432.23532,4);TRUNC(23432.23532,2);TRUNC(23432.23532, -2);

CHARACTERS
SELECT ‘HeLlo WOrlD’; UPPER(‘HeLlo WOrlD’);LOWER(‘HeLlo WOrlD’); LENGTH(‘HeLlo WOrlD’);

–DATE
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;