[ DB ] Midterm review

Querying

(b) Write a query that finds the name of each city whose population is greater than the population of all cities in the Africa continent

SELECT name
FROM city
WHERE population > ALL (
	SELECT city.population
    FROM country JOIN city ON country.code = city.countrycode
    WHERE continent = 'Africa'
);

(d) Write a query that finds each continent along with the number of countries that have a city (or cities) with a populaiton of 5,000,000

SELECT continent, COUNT(*)
FROM country JOIN city ON country.code = city.countrycode
WHERE city.population > 5000000
GROUP BY continent;

(e) Suppose that you would like to delete all records in countrylanguage whose share (persentage) is less than 1%

DELETE FROM countrylanguage
WHERE percentage < 1;

(g) Suppose that you have the same database schema in your database but no data (i.e., you have only the tables and have no data records in your database). If you would like to insert new records of language usage in North Korea, what are the SQL queries required to run? Write only the queries that you have to execute. Please indicate the order of execution in front of each query.

While you are composing queries, use following information as you need:

• The country code for North Korea is PRK
• North Korea uses Korean (99.6%), Chinese (0.2%), Russian (0.1%), and Japanese (0.1%)
• North Korea is in Asia. Its population is 25,000,000 and life expectancy is 72

INSERT INTO country
VALUES ('PRK', 'North Korea', 'Asia', 25000000, 72);

INSERT INTO countryLanguage
VALUES ('PRK', 'Korean'. 99.6), ('PRK', 'Chinese', 0.2), ('PRK', 'Russian', 0.1), ('PRK', 'Japanese', 0.1);

Result of query

(d)

SELECT SUM(sold) 
FROM album
WHERE mid NOT IN (
	SELECT mid 
    FROM artist
    WHERE origin NOT LIKE '%NC'
);

SUM(sold)
1,550,000

(e)

SELECT musician_name, position
FROM artist S
WHERE RIGHT(origin, 2) = SOME (
	SELECT RIGHT(origin, 2) FROM artist T
    WHERE T.mid <> S.mid AND T.position = 'Saxophonist'
);

musician_name	position
Thelonious Monk	Pianist

(g)

SELECT musician_name 
FROM artistS, (
	SELECT mid, MAX(sold)
    FROM album
    GROUP BY mid
    ORDER BY MAX(sold)
    LIMIT 3
) T
WHERE S.mid = T.mid;

musician_name
Thelonious Monk
Ella Fitzgerald
Duke Ellington

(h)

SLECT position,
	SUM(CASE WHEN RIGHT(origin, 2) = 'DC' THEN 1 ELSE 0 END) AS DC,
    SUM(CASE WHEN RIGHT(origin, 2) = 'IL' THEN 1 ELSE 0 END) AS IL,
    SUM(CASE WHEN RIGHT(origin, 2) = 'KS' THEN 1 ELSE 0 END) AS KS,
    SUM(CASE WHEN RIGHT(origin, 2) = 'LA' THEN 1 ELSE 0 END) AS LA,
    SUM(CASE WHEN RIGHT(origin, 2) = 'NC' THEN 1 ELSE 0 END) AS NC,
    SUM(CASE WHEN RIGHT(origin, 2) = 'VA' THEN 1 ELSE 0 END) AS VA,
    COUNT(*) TOTAL
FROM artist
GROUP BY position;

position	DC	IL	KS	LA	NC	VA	TOTAL
Trumpeter	0	1	0	1	0	0	2
Saxophonist	0	0	1	0	1	0	2
Pianist	1	0	0	0	1	0	2
Pianist	0	0	0	0	0	1	1

T / F

(a) One can store binary data objects of indeterminate and variable size, such as images, video clips, sound files, or any other kind of multimedia informationm in an R_DBMS
➡️ True

(b) The BIGINT data type in SQL does not require the user to specify the size and can represent an infinite range ofinteger numbers
➡️ False
It's 8-byte long integer: between -9223372036854775808 and 9223372036854775807

(c) While a VARCHAR type of attribute can store a variable length character sequence, it consistently uses additional space for the length information
➡️ True

⭐️ (d) The range of a NUMERIC(DECIMAL) attribute is equivalent to that of a FLOAT-type attribute
➡️ False
DECIMAL: M is the maximum number of difits (the precision). It has a range of 1 to 65 (default: 10). D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M (default: 0)
FLOAT: min = -3.402823466E+38, Max = -1.175494351E-38

(e) When reducing E-R diagrams to database table schemas, every relationship set becomes a relation
➡️ False
Only many-to-many relationship sets do

⭐️ (f) In E-R diagrams, strong entity sets refer to the entity sets that do not paricipate in any relationships
➡️ False
SES is a collection of entities whose identify is not dependent on any other entity set

(g) Normalization may increase the occurrences of data anomalies, while it improves data consistency
➡️ False
May decrease the issues with data anomalies

(h) A functional dependency is a generalized notion of a foreign key
➡️ False
A generlized notion for primary keys

(i) By committing the following decomposition, one can achieve the second normal form➡️ True

(j) By committing the following decomposition, one can achieve the third normal form➡️ False, 4NF

(k) $\Pi_{salary}(\sigma_{dept_name="Physics"}(instructor))$ in realtional algebra is equivalent to SELECT salary FROM instructor WHERE dept_name = 'Physis' of SQL
➡️ True