Show students’ first name and last name, the course numbers the student took (don’t include currently taking) and its credits, and the grades of the courses in the ascending order of last name and first name and descending order of course numberSTUDENT TABLE100001JohnsonAriel2000-07-10Y100002GreenRobin2001-11-02Y100003JohnsonCharles1995-01-12Y100004PearsonJeffery1996-02-06Y100005SearsMiguel1998-10-31Y100006KyleLeah2000-05-29Y100007MyersLynda1980-08-24Y DB Browser for SQLite - C:\Users\13309\OneDrive\Desktop\sqlite\LabAssignment3.sqliteFileEdit View Tools HelpCONew DatabaseOpen DatabaseGWrite ChangesRevert ChangesOpen ProjectSave ProjectAttach DatabaseX Close DatabaseDatabase Structure Browse DataEdit PragmasExecute SQLTable: COURSEa Fiter in any columnCourseID CourseNumber DescriptionCreditFilterFilterFilterFilter10001MISM4221 System .3210002MISM4222 System .3310003CPSC3131 Database I410004CPSC3132 Database II3510005CPSC4231 Cyber .610006MISM3136 Database310007ECON3221 Principles. DB Browser for SQLite - CAUsers\13309\OneDrive\Desktop\sqlite\LabAssignment3.sqliteFileEdit View Tools HelpoNew DatabaseOpen DatabaseWrite ChangesBRevert ChangesOpen ProjectSave ProjectAttach DatabaseX Close DatabaseDatabase StructureBrowse DataEdit PragmasExecute SQLTable:|ENROLLMENTFiter in any columnStudentID CourseID GradeFilterFilterFilter10000110001 A210000110003 NULL310000110004 A410000210006 NULL510000210004 B6.10000210005 B710000310004 NULL810000410002 A10000410003 B1010000410005 NULL1110000510001 NULL1210000610001 C1310000610003 C1410000610004 NULL

Data is coming from 3 tables . So apply inner join on 3 tables with joining condition. student…

Show students’ first name and last name, the course numbers the student took (don’t include currently taking) and its credits, and the grades of the courses in the ascending order of last name and first name and descending order of course number

Show students’ first name and last name, the course numbers the student took (don’t include currently taking) and its credits, and the grades of the courses in the ascending order of last name and first name and descending order of course number

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter4: Constraints

Section: Chapter Questions

Problem 15MC: Which of the following data dictionary objects should be used to view information about the...

See similar textbooks

Related questions

Q: Table name is : Clothing 1. Give me the sum, max and min for sales unit price. 2. Order item name…

A: Algorithm : Answer 1) find the sum , min and max of the sales unit price from the table Clothing.…

Q: For each type of aircraft, display the total distance that can be flown before refueling. Display…

A: SELECT fl_flight_no,fl_orig,fl_dest,fl_orig_time,fl_dest_time,fl_time_zones FROM flight order by…

Q: Show students’ first name, last name, the grades of the students, and the credits of the course for…

A: Select St.FristName, St.LastName, Et.Grade, C.CourseIDfrom Student St INNER JOIN Enrollment Et ON…

Q: Find the rep number, last name, and first name of each sales rep who represents at least one…

A: The, answer has given below:

Q: Display employees where the first name or last name starts with S. using sql plus hr user

A: To select data from a database, SELECT statement are used. The data is saved in a result table…

Q: Show all name and phone numbers of students and the type of donation they received including any…

A: Show all name and phone numbers of students and the type of donation they received including any…

Q: Normalize the below given Student Course Plan to First Normal Form, Second Normal Form and Third…

A: Normalization : is the process of minimizing redundancy from a relation or set of relations. 1NF :…

Q: Table EMP Column name EMPNO ENAME Column type Integer Up to 10 characters Example entry 7369…

A: Given: - The solution as per the given information

Q: List the number of customers living at each state that is included in the Customer_T table.

A: Query:-

Q: Show students’ first name and last name, the course numbers the student took (don’t include…

A: We need to write a SQL query that prints the table that has columns are- FirstName, LastName,…

Q: How many records are in the following table? EMPLOYEE EMPID EMPLNAME EMPINIT EMPFNAME EMPJOBCODE…

A: Refer to step 2 for the answer.

Q: Display the manager number and the salary of the lowest paid employee for that manager. Exclude…

A: We will write SQL query to solve the given problem.

Q: a. Insert the sample data into the tables(at least 4 rows in each tables). 3. Write Queries for the…

A: Creating tables: Creating Student table: create table Student(Given_names varchar(25),Surname…

Q: 25. For each type of aircraft, display the total distance that can be flown before refueling.…

A: 25. For each type of aircraft, display the total distance that can be flown before refueling.…

Q: Normalize the below given Student Course Plan to First Normal Form, Second Normal Form and Third…

A: From the given question we can identify the below functional dependencies which are violating the…

Q: Display only the first 100 records of students table sorting last_name from Z to A.

A: here have to determine about sql query for given database problem statement.

Q: b) Dim FirstName as FirstName = "Maisarah" c) Dim BirthDate as BirthDate = #15/08/1990# %3D d) Dim…

A: The given lines of code are related to VB .net programming language where the requirement is to…

Q: Order number and Customer number Table Order number number Customer 1234 9876 Order Table Order…

A: Table creation query: CREATE TABLE `customer` ( `CustomerNumber` int(25) DEFAULT NULL,…

Q: Question 1 Display employee ID, first name, last name, and corresponding department names. Question…

A: Query Approach: 1- As mentioned in the assignment created the database name as bank inside the…

Q: ployees ase. Calculate across all employees in this table, the average age, as of today, rounded to…

A: Here we will find average age of employee using AVG keyword and DATEDIFF function

Q: 9. Find the total amount of tax paid by resident of NJ Hint: The tax cost stored in tax_amount…

A: Inner join is used to join two tables. Tax_amount column is given , to find sum value of a column.…

Q: Find the listing of customers who did not make purchases during the invoicing period. Sort the…

A: The SQL query is given below Assumptions regarding column names (purchase date and invoicing date)…

Q: Display only the products (name and ID) that have a quantity greater than the average quantity of…

A: Displaying the products (name and ID) that have a quantity greater than the average quantity of…

Q: Find out the total sales, minimum sales, maximum sales and the number of invoices during the 2nd…

A: Overview : The COUNT() function returns the number of rows that matches a specified criterion. The…

Q: Print each employee, their wages, Hours and gross pay.

A: namesList=['Juan','Rae','Ivanna','Lilly','Robert']; #list to store names…

Q: Create the table that results from table MACHINE DIFFERENCE table BOOTH

A: Actually, database is a used to stores the data\information.

Q: Show the list of courses numbers that are taken at least once by students. The course should appear…

A: So, all the unique course numbers has to be printed So, for this you can make use of DISTINCT…

Q: Read the marks of a student for 3 courses. Calculate the average marks for the student. Check the…

A: Start Read marks of 3 courses Find average Initialize CGPA to 0 if average is in range 8-10 then…

Q: Your Name UMC Theaters Ticket Sales Theater Cocoplace Sunset Walk October November December Totals…

A: In this the data is for comparison of theaters ticket sales per month. In this situation, we cannot…

Q: The name of the students that has the highest total credit. table =student students have >>…

A: Table name - student The table student has id,name, department_name, total_credit are the entites.…

Q: Classify students according to the total number of hours they have completed. (Less than…

A: Note:- There are multiple hours completed columns, two are visible in the first screenshot, assuming…

Q: For each product, show the product ID and the number of orders placed. Show the results in…

A: Introduction: SQL (Structured Query Language) is a database computer language designed primarily for…

Q: Employee ID number Employee surname EMPNO Integer Up to 10 characters Integer Integer 7369 SMITH…

A: Given we have to develop a SQL query for the above given scenario:

Q: Show the first name and the last name of students who are in Active status.

A: Sql query for selecting the irst and last name of the student from given table ‘STUDENT’ who is in…

Q: For each type of aircraft, display the total distance that can be flown before refueling. Display…

A: Hey there, I am writing the required solution based on the above given question. Please do find the…

Q: Display the state of customers, the total and the average purchase amounts of each state in 2017…

A: Solution is in Step 2.

Q: List the names of all Staff members who hold a faculty position and have a salary of $45,000 or…

A: SQL Space: To leave the space while displaying the data ,a function called space is used. Syntax:…

Q: Enter ID, Name and Price of Product # 111 Fresh Milk 4.5 Enter ID, Name and Price of Product # 1 :…

A: Java Code: import java.util.Scanner;class Product{ //member variables private long ID;…

Q: Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)

A: Given : Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)…

Q: What is the average cost of the most popular visit reason? Name the average cost column as avgcost.…

A: Objective: A database is provided including two relations Patient and Visit. The most popular visit…

Q: Delete the employee who has the longest employment in the company using sql

A: can be done by sorting the table and then deleting the oldest date row. please see the SQL script in…

Q: Use the following tables to write the SQL queries: Table Name: Employees EMPLOYEE_ID FIRST_NAME HIRE…

A: Here the Column we need to display are first name and salary column from Employees table department…

Q: Display a list of publishers and the number of books they have published. Sort the results…

Q: Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum,…

A: max - function to get the maximum value of column min - function to get the minimum value of column…

Q: 1. List all book titles titles published between 2012 to 2020. 2. List all copies of the book titled…

A: Dear Student, As per our company guidelines we are supposed to answer ?️only first 3️⃣ sub-parts.…

Q: Normalize the below given Student Course Plan to First Normal Form, Second Normal Form and Third…

A: The given table is: Student_Car(CarPlateNo, CarDesc, CarType, BillNo, BillDate, Penalty, FinalBill)…

Q: Display last_name from students table who obtained below 75 algebra_grade or trigonometry_grade.

A: Since the question did not provide a table and its columns, a sample table Students created with…

Q: Show students’ first name, last name, their phone(s), and phone type

A: Lets see the solution.

Concept explainers

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.

Question

Show students’ first name and last name, the course numbers the student took (don’t include currently taking) and its credits, and the grades of the courses in the ascending order of last name and first name and descending order of course number

STUDENT TABLE

100001	Johnson	Ariel	2000-07-10	Y
100002	Green	Robin	2001-11-02	Y
100003	Johnson	Charles	1995-01-12	Y
100004	Pearson	Jeffery	1996-02-06	Y
100005	Sears	Miguel	1998-10-31	Y
100006	Kyle	Leah	2000-05-29	Y
100007	Myers	Lynda	1980-08-24	Y

DB Browser for SQLite - C:\Users\13309\OneDrive\Desktop\sqlite\LabAssignment3.sqlite
File
Edit View Tools Help
CONew Database
Open Database
GWrite Changes
Revert Changes
Open Project
Save Project
Attach Database
X Close Database
Database Structure Browse Data
Edit Pragmas
Execute SQL
Table: COURSE
a Fiter in any column
CourseID CourseNumber Description
Credit
Filter
Filter
Filter
Filter
10001
MISM4221 System .
3
2
10002
MISM4222 System .
3
3
10003
CPSC3131 Database I
4
10004
CPSC3132 Database II
3
5
10005
CPSC4231 Cyber .
6
10006
MISM3136 Database
3
10007
ECON3221 Principles.

DB Browser for SQLite - CAUsers\13309\OneDrive\Desktop\sqlite\LabAssignment3.sqlite
File
Edit View Tools Help
oNew Database
Open Database
Write Changes
BRevert Changes
Open Project
Save Project
Attach Database
X Close Database
Database Structure
Browse Data
Edit Pragmas
Execute SQL
Table:
|ENROLLMENT
Fiter in any column
StudentID CourseID Grade
Filter
Filter
Filter
100001
10001 A
2
100001
10003 NULL
3
100001
10004 A
4
100002
10006 NULL
5
100002
10004 B
6.
100002
10005 B
7
100003
10004 NULL
8
100004
10002 A
100004
10003 B
10
100004
10005 NULL
11
100005
10001 NULL
12
100006
10001 C
13
100006
10003 C
14
100006
10004 NULL

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used.
A unique identifier for a data row that consists of more than one field is commonly called a: primary plus key composite primary key foreign key none of the above
LX SQLLuery1.sql - ML-RefVm-820127.AdventureWorks2017 (ML-RefVm-820127student (57))* - Microsoft SQL Server Management Studio (Administrator) File Edit View Query Project Tools Window Help
In Database sql Createthefollowingtables:
Create an ERD in SQL with primary keys. ReservationID HotelID HotelAddress CheckInDate LengthOfStay RoomNumber RoomCapacity PoolInHotel HotelManager RatePerNightExclTaxExclDiscount RateType PercentDiscountOnRoomRate RoomTaxPercent RoomService CustomerID CustomerName RewardsID RewardsPointsEarned DateOfLastStay CustomeRatingOfStay
Use Mysql code 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.
Write Mysql code 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 with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
Create an SQLite Database as follows: A table called “Product” with the fields: _id, Name, StockOnHand, StockInTransit, Price, ReorderQuantity, ReorderAmount. StockOnHand – this represents how many of a Product is currently in the Warehouse/Store. StockInTransit – this represents how many of a Product has been ordered and is on its way, not yet received. Price – this is the cost of a Product. ReorderQuantity – this is the number at which stock levels must reach before a purchase order must be made. If StockOnHand is less than ReorderQuantity + StockInTransit, then a purchase order is necessary. ReorderAmount – this is the amount to be purchased when a reorder is made.
The database contains a Horse table, with columns: ID integer, primary key Registered Name variable-length string. The database contains a Student table, with columns: • ID integer, primary key First Name - variable-length string LastName variable-length string Write a SQL Query to create a Schedule table, with columns: HorseID - integer with range 0 to 65535, not NULL • Student ID integer with range 0 to 65535 Lesson DateTime - date/time, not NULL (HorseID, Lesson DateTime) is the primary key Also, create the following foreign key constraints on Schedule columns: HorseID references Horse. When an ID is deleted from Horse matching Lesson Schedule rows are deleted. Student ID references Student. When an ID is deleted from Student, matching Student ID 's are set to NULL).
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.
Database Question this is review Please keep the answer short The SQL statements that answer these questions. please keep the answer simple will give thumbs up thank you :)! INSERT STATEMENT LINK below https://drive.google.com/file/d/1qHCkrods5K2V1syyS5IG6-Eb_3QQmud7/view?usp=sharing Table.SQL below -- MySQL Script generated by MySQL Workbench-- Sun Feb 21 00:00:31 2021-- Model: New Model Version: 1.0-- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ------------------------------------------------------- Schema mydb-- ----------------------------------------------------- -- ------------------------------------------------------- Table state-- -----------------------------------------------------CREATE TABLE IF…
Assume that the Authors table is already built in the database with the following specifications: Authors: Column Name Data Type constrains Authors ID Author Name Varchar2(10) |Varchar2(30) Primary key Unique You Need to Create the Books table with the following specifications Column Name Data Type Constrains ISBN Book Title Book Price Author id Number (10) Varchar2(40) Number (4,2) Varchar2(10) Primary key Not Null referencing the Author_ID in the Author Table Complete the create statement by drag and drop the correct word: Create Table lblank (ISBN Number (10) plank, Book Title Varchar2blank ) Not Null, Book Price Number(4, 2), Auther IR blank (10) references Jolank (blank ); Author ID Books Number||Authors Not Nul Varchar2| Primary Key 40 h (United States)