Display CustomerName and Status as 'Q2 for every customer who was born during the 2nd quarter of 2020. Then, combine it with CustomerName and Status as 'Q3 2020° for every customer who was born during the 3rd quarter of 2020. (year, datename, union) CustomerName Status | Q2 2020 MsCustomer Amy Grenkov Dukey Diano 1 CustomerID char(5) varchar(50) 2 Q3 2020 E CustomerName O CustomerGender varchar(10) A CustomerAddress varchar(50) O CustomerEmail H CustomerDOB 3 Ingamar Carin Q3 2020 4 Javier Drewson Q3 2020 varchar(50) Pooh McCutcheon Q3 2020 date Westley Boram 03 2020 5.
Q: Change these SQL query statements to XML Statements select distinct company_name from (employee…
A: Extensible Markup Language (XML) Extensible Markup Language (XML) is a markup language that…
Q: Write a SQL query that displays the number of customers who born on the 16th day of a month . Write…
A: 1. select count(customerID) from Customers where to_char(custDOB,'DD') = 16; 2. select customerID…
Q: Provide a summary of customer balance characteristics for customers who made purchases. Include the…
A: Actually the given information is Minimum Balance 0.00 Maximum Balance 345.86 Average Balance 70.30
Q: Display the maximum price of all the products in PRODUCTS table for each Supplier ID having a…
A: Here we need to join Table Supplier and PID. Since they have common attribute,they can be joined…
Q: Display the PID as “Product ID”, Desc as “Description”, and SName as “Supplier Name” with their…
A: Here we can use AS operator to rename the query. Also use JOIN clause using the JOIN On condition.
Q: Write SQL queries for the following statements (Use only Cartesian product and join)…
A: Hey there, I am writing the required solution for the above stated question. I can only answer one…
Q: 1- List all the users and their card name using union 2- List all the users and their card name…
A: Answer1 : We will need to join the multiple sets to get the required details So, we will join the…
Q: the following Query on the books table from book store database. m Note: the table books consist of…
A: We use the SELECT command for this with WHERE condition on it.
Q: Employees(employee_id, first_name, last name, salary, hire_date, job_title, manager_id) Which of the…
A: working query : SELECT last_name,job_title FROM employees WHERE salary > SELECT avg(salary) FROM…
Q: 8002 1002 30/3/2020 528.10 8003 1003 12/4/2020 194.78 8004 1004 23/4/2020 619.44 1. Create both the…
A: Actually, database is a used to stores the data.
Q: Write SQL queries for the following statements (Use only Cartesian product and join)…
A: 1. Ans PNAME ((Scity= 'islamabad'(Customer)) ⋈CID (Order))
Q: PRODUCTS PID DESC PRICE SID QTY 801 Pencil 3 1 10 802 A4 6 2 15 803 Ballpen 4 2…
A: GIVEN: PRODUCTS PID DESC PRICE SID QTY 801 Pencil 3 1 10 802 A4 6…
Q: FROM SALESPERSON WHERE SPNUM= (SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772): What is this subquery…
A: Given subquery SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772
Q: 1. List the item number, description, and price for all items. 2..List all rows and.columns. tor the…
A: Note : As per company guidelines, we are supposed to answer 3 sub - parts . Please repost other…
Q: 1. Display the last name and salary for all employees whose salary is not in the range of $5,000 and…
A: As per the database question we will give the query for all of them. 1. select last_name, salary…
Q: 1. Customers and internet packages (Customers and Packages tables) a. Write a query to display…
A: On understanding the relational model from Edgar F. Codd in the early 1970s, Donald D. Chamberlin…
Q: 8- Write a query to generate the total number of invoices, the invoice total for all of the…
A: SQL is a language to operate databases in searching and filtering the data according to the query
Q: Q9) find the least expensive price Q10) List the UNIQUE v_code of vendors that provide products Q11)…
A: A relational database contains tables. Tables are also known as relations and they contain tuples…
Q: Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set…
A: insert into CUSTOMER(CUST_LNAME, CUST_FNAME, CUST_BALANCE)values('Powers', 'Ruth', 500);
Q: Please convert following figure into a relational schema. In each table, the primary key and foreign…
A: Here the BankBranch entity is a weak entity and therefore we need to include the primary key of Bank…
Q: QUESTION : 1. Write a PL/SQL PROCEDURE that will list the project names given the course ID.…
A: --Following is the code as required. Compile the procedure first and then execute the anonymous…
Q: 1- Find ( display or select) the employees who have salary greater than the salary of all employees…
A: The answer for the given question is as follows.
Q: DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 10 Administration 200 1700 Marketing 201 1800…
A: According to the asked question, the solution is given below with a proper explanation.
Q: •Create and load the Employee, Department and Titic Tavics: ton •Write Queries for the following:…
A: Since multiple queries have been asked to be answered, we will answer first three queries. If you…
Q: SUPPLIERS SID SNAME STYPE LOCATION 1 Lulu DS Salalah 2 C4 DS Saadah 3 Smile WS…
A: Here first we need to find SID of product 802. Let's name this as X. Then find productID and…
Q: For each sale made on June 1, 2015, show the following information: CustomerID, Customer name…
A: Hey, since there are multiple questions posted, we will answer first question. If you want any…
Q: 2- selects the order line items (orderNumber, orderlinenumber) from the “orderdetails” table. Also,…
A: According to our guidelines, we are allowed to solve only the first three subparts of the question.…
Q: C) Which employees earn less salary than the average salary for the consultants who perform the same…
A: According to the information given:- We have to consider the instruction to write queries on the…
Q: Salary from highest to lowest Sorting emp name alphabet
A: Please find the answer below
Q: R language Show Which department has the highest admission on UCB admission dataset available in R,…
A: Hey there, I am writing the required solution of the questin mentioned above. Please do find the…
Q: The table below lists customer/car hire data. Each customer may hire cars from various outlets. A…
A: The SQL statement to insert a record in the table is - INSERT INTO table_nameVALUES (val1, val2,…
Q: 1) Rewrite the query below to use GROUP BY CUS_CODE : SELECT CUSTOMER.CUS_CODE,…
A: In step 2, you will get the answer for 1. In step 3, you will get the answer for 2. In step 4, you…
Q: Je an expressioi relational aigebra Not on SQL ) V Find the ID and name of each employee who does…
A: Expression in Relational Algebra a) Project ID, name (SELECT Company_Name <> "BigBank"…
Q: Write the SQL code to find the average Assign_Charge by Job_code from the Assignment table. Write…
A: 1) Write the SQL code to find the average Assign_Charge by Job_code from the Assignment table.…
Q: 1. List the Customer Id, Last Name for all customers, sorted by Last Name, ID. Table "Customer Id"…
A: As per our company guidelines we are supposed to answer only first 3 sub-parts. Kindly repost other…
Q: Employees(employee_id, first_name, last_name, salary, hire_date, job_title, manager_id) Which of the…
A: query that will is: select e.last_name from employees e, employees m where e.manager_id =…
Q: Use the following table to answer the questions below: Table name: Customers Column Name Role Data…
A: As per our guidelines, we are supposed to answer only 1st 3 parts. Kindly repost the remaining…
Q: select stuid, name, stradr, city, state from student where stuid in (select stuid from registration…
A: These are all just inner queries and we are using one result in other's where condition
Q: IF POSSIBLE PLEASE TYPE OR WRITE ELIGIBLY illustrate the process of normalizing the data shown in…
A: ANSWER:-
Q: make query for: StudentID Student Name Semester Credit Semester GPA-ICXN) SC GPA (Grade Point…
A: The Final answer will contain 7 tables: StudentScoreDetail table StudentScore table Course table…
Q: SELECT e.Last_Name, m.Last_Name AS "Manager Name", e.Salary*12 AS "Annual Salary", 1.City FROM…
A: Please find all supporting table details and the output in the following steps.
Q: The following query reports the employee with the highest salary amount per postal code for…
A: SQL
Q: Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branch office…
A: Given: write the following queries (n) Identify flats with at least three rooms and with a monthly…
Q: Student(Stud_Id, name, addr, sex, DOB, country , major) Lecturer(Lect_id, name, sex, department,…
A: Relational algebra is a procedural query language which is based on relational model. The…
Q: c) Write the standard MYSQL query to retrieve the Name and CustNumber of customers with a balance…
A: In the question it is asked for retrieving the Name, But in the CUSTOMER table there is column name…
Q: Use the following table to answer the questions below: Table name: Customers Column Name Role Data…
A: Create a table Customers with the below query: create table Customers(customerID char(5) PRIMARY…
Q: STUDENT(sid, firstname, lastname, cgpa, contactno) ENROLLMENT(sid, cid) COURSE(cid, name, code,…
A: the answer is given below:-
Q: List the customer ID and first and last names for all customers located in the city of Cody. Your…
A: Convert the city to upper case and then check whether city is CODY or not
Q: Display the name of supplier(s) that supplied the most expensive computer?
A: we can't directly use aggregate functions in where clause. writing select columnName from tableName…
Step by step
Solved in 4 steps with 3 images
- 5. In the OrderItems table, display a count of the number of products where item_price is greater than or equal to $6, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BRO3 1 rows returned in 0.03 seconds PROD ID Download 4 NUM PROD IDExamine this product table's column definitions:pid number primary keypname varchar2(50)You must display column headings as shown:Product_ID Product NameWhich SELECT statement does this? SELECT pid Product_ID, pname Product Name FROM product; SELECT pid Product_ID, pname "Product Name" FROM product; SELECT pid @Product_ID, pname @"Product Name" FROM product; SELECT pid AS Product_ID, pname AS Product Name FROM productPRODUCTS PID DESC PRICE SID QTY 801 Pencil 3 1 10 802 A4 6 2 15 803 Ballpen 4 2 804 Marker 2 3 20 SUPPLIERS SID SNAME STYPE LOCATION 1 Lulu DS Salalah 2 C4 DS Saadah 3 Smile WS Awqad Display the maximum price of all the products in PRODUCTS table for each Supplier ID having a maximum price more than 5.
- Show the list of students (all attributes) who were born after 2000-01-01 and are currently in Active status in the alphabetical order of the last and the first name. Hint: To calculate easily, always use the date format YYYY-MM-DDTable : PURCHASE_ORDER ORDER_NO AMOUNT ORDER DATE CUSTOMER_ID SALESMAN_ID DELIVERY DATE 1001 300 05-03-2017 2001 3001 05-05-2017 1002 200.20 10-07-2017 2002 3003 10-10-2017 1003 600 12-06-2017 2005 3002 12-08-2017 1004 400.25 10-05-2017 2003 3004 10-07-2017 1005 500.10 03-08-2017 2004 3005 03-05-2017 1006 400 10-07-2017 2005 3006 10-10-2017 Table: PURCHASE _DETAILS ORDER_NO PRODUCT_NAME PRICE QUANTITY 1001 KEYBOARD 56 100 1002 MONITOR 36 200 1003 MOUSE 100 1004 HARD_DISK 58 300 1005 RAM 25 500 1007 CABINET 100 a) Write a SQL query to display PRODUCT_NAME, ORDER DATE and find number of years between the DELIVERY_ DATE from the CURRENT DATE [Hint: Use ON Clause]. b) Write a SQL query to display matched records from PURCHASE_ORDER table and all records from PURCHASE_DETAILS. c) Write an SQL query to display ORDER_NO, PRODUCT_NAME from table ORDER_DETAILS of products where the price is less than average price of products. [Hint: Use Subquery]. d) Write an SQL query to display ORDER_NO,…SUPPLIERS SID SNAME STYPE LOCATION 1 Lulu DS Salalah 2 C4 DS Saadah 3 Smile WS Awqad PRODUCTS PID DESC PRICE SID QTY 801 Pencil 3 1 10 802 A4 6 2 15 803 Ballpen 4 2 804 Marker 2 3 20 Display the PID and Description of products who has the same supplier as product 802. (use subquery)
- Product_id Product_name Category Mfg_date Exp_Dte Product_price 01 Dairy Milk Choclate Junk 2,Aug, 2019 2aug, 2020 80 02 Lipton Tea Bags Not junk 2,jan 2019 2, jan 2020 160 03 Kurkure junk 2, april 2919 2, april, 2021 30 04 Shezan Juice Junk 3,Aug, 2019 3,aug, 2020 30 06 Olpers Milk Not junk 3,april, 2018 3,april, 2020 350 Canteen_table 1: Write query to create canteen_table. Put check on category values as only “junk” and “Not junk”. Change colum name of product_price to unit_price. Display products manufactured between 1, jan 2019 to 1, May 2019. Display count of only junk food items in canteen_table with alias name as “junk Food Count”. Display Count Of both Junk food and not junk food products whose count is greater then 1.Problem 1. Using the INVOICE table structure shown in table below do the following: INVOICE Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2016 15-Jan-2016 15-Jan-2016 15-Jan-2016 16-Jan-2016 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75 Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one…1. Use the following table to answer the questions below: Table name: Products Column Data Type Length Constraint Role Name Stores the ID of a product P ID P_Name Primary key Number Stores the name of a Varchar2 25 product P_entryDate Stores the entry date of a product Date Cannot be null P_Expiry Stores the expiry date of a product Date P_Еxpiry date should be greater than P_entryDate P_Price Stores the price of a product Number 3,2 Cannot be null a. Write a SQL query that displays the mumber of products that will expire in the year 2021 b. Write in SQL a query that displays the ids of the most expensive products c. Create a view named ProductDetails' to hold all the details of products whose names start with 'DELL' and their prices are between 200 and 500 Omani Rials
- Display only the products (name and ID) that have a quantity greater than the average quantity of all products.Customers (cNo, cName, cSurname, cCity, cPhone, CDOB, saleRepld) saleRepld: references Salespersons(sNo) Salespersons (sNo, sName, sSurname, sCity, sDepartment, sSalary, sPhone, sHiredate) Which of the following queries displays salespersons working in departments 20, 30 or 50 in alphabetical order by surname? Select one: a.SELECT * from Salespersons WHERE SDepartment in (20,30,50) ORDER BY sSurname asc; b.SELECT * from Salespersons WHERE sDepartment = 20,30,50 ORDER BY sSurname asc; c.SELECT * from Salespersons WHERE sDepartment in (20,30,50) ORDER BY sSurname desc; d.SELECT * from Salespersons WHERE SDepartment between 20 and 50 GROUP BY sSurname asc;1. Use the following table to answer the questions below: Table name: Products Column Role Data Type Length Constraint Name Stores the ID of a product Stores the name of a product P_entryDate Stores the entry date of a | product Stores the expiry date of a product P_ID P_Name Number Varchar2 |Primary key 25 Date Cannot be null P_Expiry Date P_Expiry date should be greater than P_entryDate |Cannot be null P_Price | Stores the price of a product | Number 3,2 2. a. Write in SQL a command that creates the table Products according to the above description b. Write in SQL a statement that adds to the table Products a new column named supplierlD of type Number(5). This column should be defined as a foreign key that relates the table Products to the table Supplier c. Write in SQL a query that lists all the details of products arranged in descending order according to the entry date a. Write a SQL query that displays the number of products that will expire in the year 2021| e. Write in SQL a…