Assignment-1

Assignment-1
 Assignment-3
Query all columns for all American cities in CITY with populations larger than
100,000. The CountryCode for America is USA.
Input Format
The CITY table is described as follows:

Assignment-4
Write a query that prints a list of employee names (i.e.: the name attribute) for
employees in Employee having a salary greater than per month who have been
employees for less than months. Sort your result by ascending employee_id.
Input Format
The Employee table containing employee data for a company is described as follows:
where employee_id is an employee's ID number, name is their name, months is the
total number of months they've been working for the company, and salary is the their
monthly salary

Assignment-5
Query a count of the number of cities in CITY with populations larger than 100,000.
Query the total population of all cities in CITY where District is California.
Query the average population of all cities in CITY where District is California.
Query the average population for all cities in CITY, rounded down to the nearest
integer.
Query the sum of the populations for all Japanese cities in CITY. The
COUNTRYCODE for Japan is JPN.
Query the difference between the maximum and minimum populations in CITY.
Input Format-
The CITY table is described as follows:

Assignment-6
Julia asked her students to create some coding challenges. Write a query to print the
hacker_id, name, and the total number of challenges created by each student. Sort
your results by the total number of challenges in descending order. If more than one
student created the same number of challenges, then sort the result by hacker_id. If
more than one student created the same number of challenges and the count is less
than the maximum number of challenges created, then exclude those students from
the result.

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the
hacker.
Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the
student who created the challenge.

Hackers Table:

Challenges Table:
 Assignment-7
The following unnormalized table named PRODUCT is transformed to first normal form (1NF)
by splitting it into two tables which have X and Y rows (such that X <Y) respectively. Both the
tables have Z columns.

*Product-ID* *Colors* *Price*

What are the values of X, Y, Z? Enter these integers, each on a new line, in the text-box below.
Do not leave any leading or trailing spaces.

Assignment-8
A particular database is normalized to satisfy a particular level of normalization (either 1NF or
2NF or 3NF). One of the tables contains, among other fields, a column for the City and a column
for the Zip Code. Assuming that there is a many-to-one mapping between the set of Zip Code(s)
and City, we may conclude that the database is definitely NOT in NF form. What is the integer x
(1, 2, or 3)? Fill your answer in the text box below.

Assignment-9
A database used by a college’s application stores the relationship between students and the
courses they are enrolled in. We have information for each STUDENT (such as name, date of
birth, date of enrollment, student-id) and COURSE (course code, instructor, etc.). In real life, a
student takes several courses simultaneously while a subject is studied by several students. We
need to capture this many-to-many relationship in our database. From the above information,
what is the minimum number of tables required to structure this database in accordance with the
rules of 2NF normalization?

Assignment-10
A database, normalized as per 2NF rules, has been split into 10 tables. Each of the tables has
exactly two columns: one key attribute and one non-key attribute. What is the minimum number
of tables required to express this database in 3NF form?

Assignment-11
Write a query that prints a list of employee names (i.e.: the name attribute) for employees in
Employee having a salary greater than per month who have been employees for less than
months. Sort your result by ascending employee_id.
Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee
table in alphabetical order.

Assignment-11
Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number
of months they've been working for the company, and salary is their monthly salary.

Sample Input
 Assignment-12
Query a list of CITY names from STATION with even ID numbers only. You may print the
results in any order, but must exclude duplicates from your answer.

Input Format

The STATION table is described as follows: