Flashcards
SQL
Cards in this deck (22)
- 1Select all employees ordered by their salaries. In the case of a tie, order by their last name1 SELECT *
2 FROM employee
3 ORDER BY hour_salary, last_name - 2Describe the INTERSECT set operation1 SELECT...
2 INTERSECT[ALL]
3 SELECT..
The query returns distinct rows that are in both result.
EX: If we have two A's in both tables and a C in only one of them => two A's in the resulting column (when ALL is selected). When ALL is not selected we will only have one A. Never a C. - 3Describe the EXCEPT set operationReturns distinct rows from the first result set which are not in the second result set.
EX: table 1 = (AAABC) table 2 =(AAB) => EXCEPT: C - 4Describe the EXCEPT ALL set operationEXCEPT ALL: The query returns rows from the first result set which are not in the second result set. (If a given row is in the first result set c1 times and in the second result set c2 times, it will be in the result c1 − c2 times if c1 > c2, and not at all otherwise.)
EX: table 1 = (AAABC) table 2 =(AAB) => EXCEPT ALL: AC - 5What does the function count(*) do when used with select?Example: SELECT salary ... returns rows:
45, 36, 40, 45, 50, NULL, 73, 40, NULL, 46
=> count(*) = 10 Number of rows is 10.
Count conts the number of rows, even NULL valued.f - 6Group by syntaxSELECT select_expr[,...]
FROM table_reference[,...]
WHERE [where_condition]
GROUP BY group_expr[,...]]
ORDER BY order_expr [ASC|DESC] [,...] ] - 7The total number of employees and the average salary PER department.SELECT department_id, count(*), avg(hour_salary)
FROM employee
GROUP BY department_id - 8What does the HAVING clause do?To choose the groups that will be included in the result. For ex:
GROUP BY department_id
HAVING count(*) >= 10
OBS - Having körs i slutet, dvs när vi har kört alla kommandon har vi en ny tabell. Det är den having count(*) körs på. Dvs den tabellen man räknar alla rader på. - 9Outer join vs inner joinInner joins like
1 SELECT *
2 FROM A
3 JOIN B ON B.attr_b = A.attr_a
returns “only” combinations of rows in A and B that match the condition.
While in outer join:
SELECT *
FROM A
LEFT JOIN B ON B.attr_b=A.attr_a
includes also all rows in A with no matching row in B (values for attribute of B are NULL - 10right outer joinSELECT *
FROM A
RIGHT JOIN B ON B.attr_b=A.attr_a
includes rows in B with no matching row in A (values for attribute of A are NULL). - 11HAVING vs WHEREHAVING används vid count, sum osv (aggregated functions). Du kan INTE köra WHERE på ett resultat av en aggregated function.
- 12WHERE NOT EXIST (sub query)Om sub query returnerar en tom tabell => true, annars false
- 13WHERE Country NOT IN ('Germany', 'France', 'UK');Returns all Countrys != 'Germany', 'France', 'UK'
OBS - NOT IN can also be applied to sub queries. - 14Why shouldn't you use count with select?if you have a table T1 with a column with 4 rows: A A B B
If you select that column you get A A B B, but count on that column (or any other column) will just return ONE number. I.E the number of rows wont match. - 15expr = ANY (nested_query)expr IN (nested_query).
True iff all values returned by the nested query meet the condition. - 16expr <> ALL (nested_query) is equivalent toexpr NOT IN (nested_query).
True iff all values returned by the nested query meet the condition. - 17ON DELETE CASCADE means?A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
- 18What is the difference between SELECT COUNT(*) and COUNT()COUNT(*) will return the total of all records returned in the result set regardless of NULL values.
But when you specify the column NULL fields will be ignored. - 19TABLE 1: Field1 Field2 Field3 --------------------------- 1 1 1 NULL NULL NULL 2 2 NULL 1 1 1 What will this query return? SELECT COUNT(*), COUNT(Field1), COUNT(Field2), COUNT(Field3) FROM Table1COUNT(*) = 4; -- count all rows even null/duplicates
COUNT(Field X) - counts only rows without null values on that field
COUNT(Field1) = COUNT(Field2) = 3
COUNT(Field3) = 2 - 20What happens when you have a group by clause with several columns? EX: SELECT FirstName, LastName, City, Country FROM Customer ORDER BY Country, CityThe query will list all customers ordered by country, then by city WITHIN each country
- 21The operator <> means?not equal to
- 22What is the difference between IN and ANY in SQL?You must place an =, <>, <, >, <=, or >= operator before ANY.
You can not use comparative operators with IN
Information
- Description
- Databas Flashcards on SQL, created by Emma Tysk on 19/12/2017.
- Created by
- Emma Tysk
- Views
- 1
- Rating