Write the SQL code
Q: BOOK_NUM Times Checked Out BOOK_NUM TIMES Checked Out 5236 12 5236 12 No under score line 5235 5235…
A: SELECT BOOK_NUM, COUNT(check_out_DATE) AS "Times Checked Out " FROM CHECKOUT GROUP BY BOOK_NUM…
Q: Write a query to list the book number, title, and number of times each book has been checked out…
A: Query The query for viewing book number, title and the number of times each book had been checked…
Q: Using the university schema, write an SQL query to find section(s) with maximum enrollment. The…
A: Select courseid, secid, year, semester , Count( snum) From Section Group By courseid Order by…
Q: select em.ENAME,em.SAL from SCOTT."EMP" em where em.ENAME like '%jon%'; but i need it using soundex…
A: SQL is a structured query language, it's is used to extract and manipulate the data from the table.…
Q: Using the university schema, write an SQL query to find the number of students in each section. The…
A: SQL Query: SELECT s.course_id, s.sec_id, s.year, s.semester, count(id) AS numFROM takes t, section…
Q: • Write an SQL query to fetch last_name from employees table using the alias name as Employee Name.…
A: 1ST Query Answer: SELECT last_name AS employee_name FROM employees; Explanation: it will print…
Q: Write SQL code that will return the total number of hours worked for each employee and the total…
A: SQL code: The COUNT function calculates the number of numeric values in a list of inputs. COUNT…
Q: SQL Query do the following
A: "SELECT" command:The "SELECT" command is used to retrieve data in a database. The syntax for…
Q: write sample sql quiries for these Write a query to display employee number, first name, job id…
A: Please refer below for your reference: Queries are: 1. SELECT employee number, first name, job id…
Q: Write a query that displays name of employee and commission for all employees who have been employed…
A: select ename,nvl(to_char(comm),'NO COMM') as comm,round(months_between(sysdate,hiredate))as…
Q: Create a PL/SQL query to display the invoice number, coin name, coin price and delivery notes. In…
A: create the table Bill which contains the following fields, Bill number product price delivery note…
Q: Write SQL queries for the following. Display details of those patients whose age is either 25 or 36…
A: Since you have posted a question with multiple sub-parts, according to the company's policy we will…
Q: Select clause and from clause: Display the columns specified above in the problem statement from…
A: Write a query to display the author ID, first name, last name, and year of birth for all authors…
Q: Write a query to display the author ID, first and last name for all authors who have never written a…
A: Approach first we gonna to select all those book those are written in programing subject.(sub-query)…
Q: Using the university schema, write an SQL query to find section(s) with maximum enrollment. The…
A: EXPLANATION: Step 1: We have details of the section in the section table and students who are taking…
Q: Write a query to display the patron ID, book number, and days kept for each checkout. "Days Kept" is…
A: In SQL, to calculates the number of days between two dates, DATEDIFF() function is used. It retrieve…
Q: Write the SQL code required to list the first name and last name of all customers whose middle…
A: Writing the SQL code required to list the first name and last name of all customers whose middle…
Q: Complete PL/SQL function below, which will return last name of employee whose id is equals to 2…
A: The above question is solved in step 2:-
Q: Select clause: Display the columns specified above in the problem statement From: LGPRODUCT Where…
A: SQL is a structured query language, that's is used to extract and manipulate the data from the…
Q: write the query that updates the manager of the female employee who has 'c' as the last letter of…
A: Given : write the query that updates the manager of the female employee who has 'c' as the last…
Q: Create a procedure in PL/SQL takes Department Id and return record data type has maximum salary,…
A: Find the required procedure in PL/SQL given as below :
Q: Determine the profit of each book sold to Jake Lucas, using the actual price the customer paid (not…
A:
Q: Write SQL Command to display all employees details whose job id is either AD_VP or PU_MAN. Order the…
A: We are given Employess table which contain all the attributes asked in query, so we will use…
Q: Write a query to display the author ID, first and last name for all authors that have never written…
A: We indeed to provide SQL query to display the author ID, first and last name for all authors that…
Q: Create table sales ( cust varchar(20), prod varchar(20), day integer, month integer, year integer,…
A: Answer
Q: Write and SQL query using the MAX operator to show all data from the Order Items table in which the…
A: The Structured Query Language (SQL) query to display all the data from the Order items table in…
Q: Select clause: Display the columns stated in the problem statement From clause: AUTHOR table…
A: SQL is a structured query language, that's is used to extract and manipulate the data from the…
Q: Write the sql command to show all the employees that are found in this table.
A: SELECT statements: An SQL SELECT statement retrieves records from a database table according to…
Q: 1. Use Employees table a) To display First_Name, Salary and Department_id ofall the employees, whose…
A: 1. A) select First_name, Salary, Department_idfrom Employeeswhere Department_id IN(50,80)…
Q: Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by…
A: Explanation: Join is a relational operation, which combines the data from two or more tables into…
Q: In the SQL Oracle Application, Within the Employees table, each manager_id is the manager of one or…
A: Answer: SELECT manager_id, job_id, SUM(salary) "total salary", GROUPING(manager_id),…
Q: Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should…
A: ORDER BY Clause: SQL contains “ORDER BY” clause in order to sort rows. The values get sorted in…
Q: The given SQL creates a Movie table and inserts some movies. The SELECT statement selects all…
A: Answer
Q: Write SQL queries for the following. Display details of those patients whose age is either 25 or 36…
A: SQL is a structured query language, that's is used to extract and manipulate the data from the…
Q: January
A: Here, there are 4 questions based on the Database Management System and should be solved by SQL…
Q: Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should…
A: UPDATE ASSIGNMENT SET CALC_ASSIGN_CHARGE = ASSIGN_CHG_HR* ASSIGN_HOURS;
Q: Write a query to display the author ID and the number of books written by that author. Sort the…
A: The problem is based on writing a query for MYSQL database table.
Q: Write a SQL query that selects OrderID and OrderDate from the Orders table and OrderDetaillD from…
A: Introduction: Structured Query Language (SQL) is an acronym for Structured Query Language. SQL is a…
Q: Write a query to display the patron ID, book number, and days kept for each checkout. “Days Kept” is…
A: NOTE: - AS THE SCHEMA WASN’T PROVIDED, THE NEW SCHEMA IS ASSUMED WITH THREE RECORDS.Select is used…
Q: Write an SQL query using the NOT operator that will show all employeeinformation from the Employee…
A: SQL query: SELECT * FROM Employee WHERE NOT Employee_Number = 7344;
Q: Create a PL/SQL query to display the combined customer name, coin purchased, coin price and the…
A: given Data is
Q: List the ProductName, Quantity ordered, customer LastName, FirstName, and the OrderNumber for all…
A: Each and every organization contains a certain amount of data that needs to be stored for future use…
Q: Write a query in SQL code for: "List the book categories and the average book price of each…
A: Select: Select is used to display columns or attributes that are to shown as a result of the…
Q: The company wants to see the total sale amount per sales person (salesman) for all orders. Assume…
A: SELECT NVL(a.SALESMAN_ID, 0), SUM(b.UNIT_PRICE*b.QUANTITY) As TotalPrice FROM ORDERS a, ORDER_ITEMS…
Q: Write the SQL query to get the order number, order date, customer name, shipped date, and sales…
A: Since the data spans the following tables -orders -orderdetails -products -productlines -customers…
Q: QL DATABASE need the SQL statements FROM ATTACHED tables List the trip name of each trip that…
A: According to the question below the solution: As per our company guidelines we are supposed to…
Q: write the sql code required to list the employee number, last name, first name, and middle initial…
A: The structured Query Language (SQL) code to list the employee number, last name, first name, and…
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity.
In the SQL Query do the following
Select clause: Emp_Num, Emp_LName, Emp_FName, Emp_Initial
From: EMPLOYEE
Where clause: last name starts with Smith. (You will be using Like operator)
Order by clause: Emp_Num
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
- Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of every passenger together with their flight number and the associated company. Retrieve all flights departed from all airports in London. Retrieve the ticket numbers…Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions:1. Retrieve all rows in Airport table for all the airports in London city.2. Retrieve all British and German passengers.3. Retrieve all names of all the passengers.4. Retrieve the flight number, departure and arrival airports of all British Airways.5. Retrieve the name of every passenger together with their…Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve all rows in Airport table for all the airports in London city. Retrieve all British and German passengers. Retrieve all names of all the passengers. Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of…
- Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: please if you could give all the sql statements needed i will appreciate that, and i promise to put thump up Transaction SQL Statement Retrieve all rows in Airport table for all the airports in London city. Retrieve all British and German passengers. Retrieve all names of all the passengers.…In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. Here is the table structure: CREATE TABLE USER_ ( USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (USER_ID) ); CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL, PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR ( SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),…
- Write Mysql code for: The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.One of the junior database developers has encountered some errors in a query they are working on. The junior developer has requested some assistance from you to solve their errors. Consider the following PL/SQL query: turn serveroutput ondeclaring cust customer.first_name%Type;prod product.product%Type;cursor info isselect cust.first_name || ', ' || cust.surname CUSTOMER, p.productfrom customer cust, billing b, product_billing pb, product pwhere cust.customer_id = b.customer_idwhere b.bill_id = pb.bill_idand p.product_id = pb.product_idand p.price > 10000ordering by p.price;beginsfor rec in infoloopingcust:= rec.CUSTOMER;prod:= rec.product; dbms_output.put_line('CUSTOMER: ' || cust || chr(13) || 'PRODUCT: ' || prod);dbms_output.put_line('------------------------------------');end loop;end; Q.2.1 The code above has errors. Identify the errors and rewrite the code so that it would produce a valid output for the junior database developer.The following three tables make up a simple reservation system for a small campground. The database should allow for a camper to make multiple reservations for future dates and for a camping spot to have several reservations for upcoming visits. Table: Camper CAMPER_NUMBER 1000 1001 1002 1003 CAMPER_LAST_NAME Jones Schmidt Williams Cooper CAMPER_FIRST_NAME Jamie Pat Clifford Amanda CAMPER_ADDRESS 1278 Essex Pl 4954 Spangled Way 956 Segull Lane P. O. Box 998877 CAMPER_CITY Birmingham El Paso Portland Portsmouth CAMPER_STATE AL TX ME OH CAMPER_ZIP_CODE 45251 79919 4108 45662 CAMPER_DRIVERS_LICENSE JJ998743-98 87632434 WIL885123 765A876B897 CAMPER_EMAIL jjones@somewhere.com patwonderfu34l@nowhere.net williams98342@foomail.com coopera@nowhere.net Table: Spot SPOT_NUMBER 101 102 103 104 SPOT_NAME The Pines The Glade Teardrop Spot Tent In Trees SPOT_LENGTH 55 50 20 0 SPOT_PULLTHRU 1 0 0 0 SPOT_ELECTRIC_AMPS 50 50 20 0…
- The following three tables make up a simple reservation system for a small campground. The database should allow for a camper to make multiple reservations for future dates and for a camping spot to have several reservations for upcoming visits. Table: Camper CAMPER_NUMBER 1000 1001 1002 1003 CAMPER_LAST_NAME Jones Schmidt Williams Cooper CAMPER_FIRST_NAME Jamie Pat Clifford Amanda CAMPER_ADDRESS 1278 Essex Pl 4954 Spangled Way 956 Segull Lane P. O. Box 998877 CAMPER_CITY Birmingham El Paso Portland Portsmouth CAMPER_STATE AL TX ME OH CAMPER_ZIP_CODE 45251 79919 4108 45662 CAMPER_DRIVERS_LICENSE JJ998743-98 87632434 WIL885123 765A876B897 CAMPER_EMAIL jjones@somewhere.com patwonderfu34l@nowhere.net williams98342@foomail.com coopera@nowhere.net Table: Spot SPOT_NUMBER 101 102 103 104 SPOT_NAME The Pines The Glade Teardrop Spot Tent In Trees…Access Assignment Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70…Db&__Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions:1. Retrieve all rows in Airport table for all the airports in London city.2. Retrieve all British and German passengers.3. Retrieve all names of all the passengers...