Databases | SQL | Oracle | MS Access Answers

Describe in detail Variables in SQL?

Task 3: Group Function and Group Rows

1.    Display how many employees with salary that is less than 6000 in each job id, in each department.

2.    Display the average, maximum and minimum salary of all employees under a manager with ID 110 or 150

3.    Display last name, first name, department, city of all employees working with manager of the department with id number 103 or 205.

4.    Count how many employees working in each country per department. Display only with number of employees of more than 20.

Task 2: Conversion Function

1.    Display last name, first name and hire date of all employees hired before 2008. Display the hire date in this format (example: "January 25, 2018”)<o:p></o:p>

2.    Displays the last names of employees under the manager id 124 or 122. Also generate an email add from getting the first 4 letters of first name of each employee.

Example: mrico@adpoly.ac.ae

Task 1: Single Row Function

1.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees that belong to a department with Department ID 20 and whose Job ID has a substring “rep”.

2.   Write an SQL statement that will display the Employee ID, Last name, First name, Job ID and salary of “GrantDouglas”.

3.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees that belong to a department with Department ID 20 and whose remainder of their salary from 3000 is greater thn 500.

4.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees working in the company for more than 15 years.

Given the schema:

Pizzas (

pizza TEXT,

PRI KEY (pizza)

);

Restaurants (

rname TEXT,

area TEXT not null,

PRI KEY (rname)

);

Customers (

cname TEXT,

area TEXT not null,

PRI KEY (cname)

);

Sells (

rname TEXT,

pizza TEXT,

price INTEGER not null,

PRI KEY (rname,pizza),

FK (rname) REF Restaurants (rname),

FK (pizza) REF Pizzas (pizza)

);

Likes (

cname TEXT,

pizza TEXT,

PRI KEY (cname,pizza),

FK (cname) REF Customers (cname),

FK (pizza) REF Pizzas (pizza)

);

A customer C is happy if for each pizza P that C likes, there are at least two restaurants, which are located in the same area as C, that sells P.

E.g. if Alice likes only pizzas P1 and P2, then Alice will be happy if

(1) restaurants RA and RB both sell P1;

(2) restaurants RC and RD both sell P2; and

(3) restaurants RA, RB, RC and RD are all located in the same area as Alice.

It doesn't matter whether {RA,RB} and {RC,RD} are the same or different set of restaurants.

Find all happy customers(cname). Exclude customers who do not like any pizza.

Question 3 – Entity Relationship Diagrams (Marks: 45)

Dineo has given what she wants to display on the website a lot of thought. And she has come up with quite a comprehensive set of business rules.

Launch Data To Be Displayed

1. A launchpad hosts multiple launches over time, and each launch is hosted by exactly one launchpad. Launchpads will be created in the database even before the first launch is hosted there.

2. A launchpad is located in a country, and a country in turn is located on a continent.

3. Each launchpad has a name and a location.

4. A launch requires three things: a launchpad that hosts it, a launch vehicle (the rocket), and the payload.

...

Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation, representing these business rules. Your design should be at the logical level – include surrogate primary key and foreign key fields and remember to remove any many‐to‐many relationships.

Get all the distinct user_ids who liked at least one video uploaded by Android Authority Channel (channel__id = 364) but didn't like the video uploaded by Tech savvy channel with video_id = 1005.

Get all the recent movie trailers that have more than 1 lakh views.

Note:
Consider the videos that have "trailer" in theirnameas trailers.
Sort the output in the descending order ofno_of_viewsandpublished_datetime
Expected Output Format
name	channel_id	no_of_views	published_datetime
...      ...           ...           ....

Question 2

Types of Databases (Marks: 10) After carefully studying the infographic, Dineo now needs to choose whether to use a relational database or a NoSQL database. She says that she needs to store the following information:

• For each launch, she needs to know who built the vehicle, where it was launched from and when. For crewed missions, she needs to know who the astronauts were that were on board.

• Each launch also has multimedia that should be included – a text description, photos, maybe even videos. Explain to her which type of database (relational or NoSQL) you would recommend, and why, for each of the following types of data.

Q.2.1 Launch data. (5)

Q.2.2 Multimedia related to the launch. (5)

what is recursive definition defined in detail with examples?