Homework1Solution

SQL QUERIES Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID,DEPARTMENT_NAME)Department(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)Location(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) Write a Query in SQL to delete the record of all those cities where city name ends with b fromlocations table.

SQL QUERIES Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID,DEPARTMENT_NAME)Department(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)Location(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) Write a query in SQL to display the DEPARTMENT_NAME of all those Departments whoseDEPARTMENT_ID is greater than the DEPARTMENT_ID of Physics department. Write a Query in SQL to show the FIRST_NAME of all those employees whose salary is lessthan the salary of all those employees whose MANAGER_ID is 101 or 102.

Write a PL/SQL block that uses searched CASE statement to classify students based on their scores in a certain test according to the following table:   Score Action >= 90 A scholarship is given 80-89 A certificate is given 70-79 A training course is recommended <70 The course should be repeated   Write the searched CASE structure only.

relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: Read a Book id provided by user at run time and store it in a variable. Fetch the Author and title of the book having the entered id and store them inside two variables Display the fetched book author and title.

WorksOn Database: Employee (EmployeeNumber, EmployeeName, BirthDate, Title, Salary, DepartmentNumber) Project (ProjectNumber, ProjectName, Budget, DepartmentNumber) Department (DepartmentNumber, DepartmentName, ManagerNumber) WorksOn (EmployeeNumber, ProjectNumber, Responsibility, Hours) Questions: 1) Write an SQL query that returns the project number and name for projects with a budget no greater than $1,050,000. 2) Write an SQL query that returns all works on records where hours worked is less than 5 and the responsibility is 'Manager'. 3) Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and make more than $50,000. 4) Write an SQL query that returns the employees (name and salary only) in department 'D1' ordered by decreasing salary. 5) Write an SQL query that returns the departments (all fields) ordered by descending department name.

J SHORTAND NOTATION FOR RELATIONAL SQL TABLES Notation Example Meaning Underlined A or A, B The attribute(s) is (are) a primary key Superscript name of relation AR or AR, BR The attribute(s) is (are) a foreign key referencing relation R As an example, the schema R(A, B, C, D, ES) S(F, G, H) corresponds to the following SQL tables: CREATE TABLE R ( A <any SQL type>, B <any SQL type>, C <any SQL type>, D <any SQL type>, E <any SQL type>, PRIMARY KEY(A), FOREIGN KEY (E) REFERENCES S(F) ); CREATE TABLE S ( F <any SQL type>, G <any SQL type>, H <any SQL type>, PRIMARY KEY(F)) EXERCISE Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards generating them [the used notation is explained above]. SHOPPINGTRANSACTION (TransId, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreIdSTORE) CREDITCARD (CardNbr, CardTypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit)…

: Write SQL queries DDL Queries to implement the following Relational Model i.e. create the Nurse and Care_Center relations (including primary key and foreign key). Intelligently write data types. Show Output.

SQL ( ORACLE SQL DEVELOPER ) :  THIS IS FIRST , SECOND AND THIRD TABLE for the Task 1 to solve  :    create table author_45( auth_name char(50), gender char(1), age number(3), nationality char(50), PRIMARY KEY (auth_name) );     create table book_45( isbn number(10), title char(50), ver_no number(2), publisher char(50), PRIMARY KEY (isbn) );     create table writtenBy_45( isbn number(10), auth_name char(50), yr_published number(4), price number(3), no_copy number(4), PRIMARY KEY (isbn,auth_name), foreign key (isbn) references book_45, foreign key (auth_name) references author_45 );     insert into author_45 values ('Benoit','F',42,'French'); insert into author_ 45 values ('Antonini','M',33,'Italian'); insert into author_ 45 values ('McAven','F',31,'Australian'); insert into author_ 45 values ('Williams','M',90,'British');     insert into book_45 values (101,'atabases',1,'Possum'); insert into book_45 values (202,'SQL Primer',2,'Hall'); insert into book_45 values…

Write the appropriate SQL query for each of the following questions: 1) Display details of ALL the clubs running by the college 2) Display the details of the IT club (IT is the name of the club)

pls help me : Write SQL queries to create tables with keys and relations corresponding to each of the following question

SQL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)A: Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_ID of all thosedepartments whose minimum salary is greater than 4000 in ascending order. B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table. C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019

SQL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)A: Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_ID of all thosedepartments whose minimum salary is greater than 4000 in ascending order.

help me with database sql exercise

Write the appropriate SQL query for each of the following questions: Display the number of students acting in ‘Radio Club’ Display the sum of fees of activities organized by the IT club

Write the appropriate SQL query for each of the following questions: Display the number of students acting in ‘Radio Club’ Display the sum of fees of activities organized by the IT club

Database course:  Write SQL queries to do the following    A. List names of IT students.        B. List titles of courses of type E.         C. Display the results of student with ID 201006      D. List student names and the courses they took.      E. List total credits that student with ID 201002.

Write the appropriate SQL query for each of the following questions:  A) Display ALL the activities organized by the IT club B) Display the number of students who are acting in each club.

Given the following relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: Read a Book id provided by user at run time and store it in a variable. Fetch the Author and title of the book having the entered id and store them inside two variables Display the fetched book author and title.

SQL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table.

sql Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)A: Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_ID of all thosedepartments whose minimum salary is greater than 4000 in ascending order. B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table. C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019

Write a PL/SQL block that uses a searched case statement to classify students based on their scores in a certain test according to the following table. Score Action 90 80-89 A certificate is given 70-79 A training course is recommended <70 A scholarship is given The course should be repeatcd NB: Write the searched CASE structure only. Use the editor to format your answer

Q2: Design Database for the following scenario and Write SQL queries. Create an ERD for the following scenario. Suppose there is a grocery store near your house. Following can be considered for ERD: A grocery store may have more than one employee. A grocery store has exactly one manager. The manager has one or more sales men working under him. Grocery store has more than one portion for the products. • Each product has a barcode, name, expired date. Many customers can busy many products, but each product is bought by only one customer. Each customer will get an invoice for his /her purchase. The bill invoice has an id.

Student(Id, name, age, class, grade)   Write SQL server query to retrieve all the student in class 6

Given the following relation: Movie (MovieID, Title, Genre, Year) Write a PL/SQL block of code that performs the following tasks: Read a movie id provided by user at run time and store it in a variable. Fetch the Genre and title of the movie having the entered id and store them inside two variables Display the fetched movie Genre and title.

PL/SQL Question I need to build pl/sql block that prompts a user for the number of visitors each day for the past 5 days and then displays the average number of visitors per day.  For example;  day 1: 19 day 2: 21 day 3: 23 ... The avg number of visitors is: ___    like this.

Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.   Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR Verify that insert has been done successfully by issuing a select * from doctor.

INFO 2303  Database Programming Assignment  : PL/SQL Practice   Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.   Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.

Part II (50 points, each problem is worth 5 points) Write SQL queries for the following database: id Customers name email id Categories name Ho Ho 8 Orders id customer_id B id Products category_id name price но HO Order_Product id order_id product_id quantity 1. Display all customers whose email is null or ends with 'fiu.edu' or 'gmail.com' 2. Display the number of orders placed by a customer whose name is ‘Jason Bourne' (assume there is only one customer with this name). 3. Insert a new product (category_id = 1, name = 'apple', price = 3.00) into the product table. Assume Products.id has a default value. 4. Create the Order_Product table as follows: id is the primary key and its data type is serial, quantity is an integer which doesn't allow null, order_id and product_id are foreign keys and will be set to null on delete. 5. Display the average price of all the products. For example, there are only three products in the database with a price of 7, 8 and 9. Then the average price would…

In SQL  This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text):• Customers: stores customer’s data• Products: stores a list of scale model cars• ProductLines: stores a list of product line categories• Orders: stores sales orders placed by customers• OrderDetails: stores sales order line items for each sales order• Payments: stores payments made by customers based on their accounts• Employees: stores all employee information as well as the organization structuresuch as who reports to whom• Offices: stores sales office data Write SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. The steps of adding a sale order are described as follows:(1) Get latest sale order number from “orders” table, and use the next sale ordernumber as the new sale order number(2) Insert a new sale order into “orders” table for the customer (customerNumber =145). For this order, the…

In SQL  This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text):• Customers: stores customer’s data• Products: stores a list of scale model cars• ProductLines: stores a list of product line categories• Orders: stores sales orders placed by customers• OrderDetails: stores sales order line items for each sales order• Payments: stores payments made by customers based on their accounts• Employees: stores all employee information as well as the organization structuresuch as who reports to whom• Offices: stores sales office data Write SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. The steps of adding a sale order are described as follows:(1) Get latest sale order number from “orders” table, and use the next sale ordernumber as the new sale order number(2) Insert a new sale order into “orders” table for the customer (customerNumber =145). For this order, the…