code of create table and insert table only please Create the following table in your database with the following schema:Table: DepartmentsI Column Name| TypeI idI nameint| varchar I+-------+id is the primary key of this table.The table has information about the id of each department of a university.Table: Students| Column Name| ТуреI id| name| department_id | int| intI varchar I+--------id is the primary key of this table.The table has information about the id of each student at a university andthe id of the department he/she studies at.Add the following data to your tables:Input:Departments table:+----I idI name+----| Electrical Engineering| Computer Engineering| Bussiness Administration || 1| 13+----+----Students table:+------+-| idI name| department id |+-----| Alice| Bob| Jennifer | 13| John| Jasmine| Steve| Luis| Jonathan | 7| Daiana| Madelynn| 1| 23| 1| 1| 14| 77I 74| 1| 2| 3| 33| 11+------ Write an SQL query to find the id and the name of all students who are enrolled in departments thatno longer exist.Return the result table in any order.The result should be:Output:+----| id| name+---| John| Daiana| Jasmine| Steve| 2| 7| 3+------Explanation:John, Daiana, Steve, and Jasmine are enrolled in departments 14, 33, 74, and 77 respectively.department 14, 33, 74, and 77 do not exist in the Departments table.

Required statement table creation and inserting data in university database given below.

Create the following table in your database with the following schema: Table: Departments Column Name Туре | id | name int | varchar | +---- -+---------+ id is the primary key of this table. The table has information about the id of each department of a university. Table: Students Column Name 1 Туре id | int | varchar I name department id | int +--- --------+ id is the primary key of this table. The table has information about the id of each student at a university and the id of the department he/she studies at. Add the following data to your tables: Input: Departments table: +----- | id | name +----- | 1 Electrical Engineering Computer Engineering Bussiness Administration | | 13 +- Students table: +----- | id | name department id | +---- 23 Alice | 1 | 1 | 5 | 2 | Bob | Jennifer | 13 | John 14 | 77 I 74 | 1 4 Jasmine | 3 Steve | Luis | 8 Jonathan | Daiana | Madelynn | 1 | 33 11

Create the following table in your database with the following schema: Table: Departments Column Name Туре | id | name int | varchar | +---- -+---------+ id is the primary key of this table. The table has information about the id of each department of a university. Table: Students Column Name 1 Туре id | int | varchar I name department id | int +--- --------+ id is the primary key of this table. The table has information about the id of each student at a university and the id of the department he/she studies at. Add the following data to your tables: Input: Departments table: +----- | id | name +----- | 1 Electrical Engineering Computer Engineering Bussiness Administration | | 13 +- Students table: +----- | id | name department id | +---- 23 Alice | 1 | 1 | 5 | 2 | Bob | Jennifer | 13 | John 14 | 77 I 74 | 1 4 Jasmine | 3 Steve | Luis | 8 Jonathan | Daiana | Madelynn | 1 | 33 11

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: Truncate the BOOK_PRICING table, and then verify that the table still exists but no longer contains…

A: In the SQL the Truncate command is used to delete the entire data of a table without deleting the…

Q: Alter the INVOICE_LINE table by adding the INVOICE_NUM column as a foreign key referencing the…

A: Alter table : used to modify column, add column/columns, add constraints, drop column, rename, drop…

Q: CREATE TABLE PRODUCT (PRODUCT_ID INTEGER PRIMARY KEY, "NAME" TEXT NOT NULL, PRICE REAL NOT NULL);…

A: As per the given information, we need to write a SQL query that can be used to return the second…

Q: Insert rows into Horse table Using MYSQL The Horse table has the following columns: ID -…

A: Find the required query to insert given data given as below :

Q: Modify customer 1000 to indicate the date of birth on March 15, 1989.

A: Modifying table The UPDATE command is used to update information present in a table. It is always…

Q: This code isnt working. Please fix it to help create the table CREATE TABLE LINE ( INV_NUMBER…

A: Step 1: There are a few issues with your SQL CREATE TABLE statement that need to be addressed: 1.…

Q: Add a CHECK constraint to the orders table requiring the quantity field to be greater than or equal…

A: CHECK constraint: A check constraint is a type of veracity constraint in SQL…

Q: Write to code to create the Table STUDENT. Use the following column names and date type: STUDENTID…

A: Since you have not mentioned , above In which subject you want a table to be created . Two subjects…

Q: Country TLD Name Code .pe Peru 604 .cy 65 .ch Switzerland 756 Cyprus 196 Complete the UPDATE…

A: The question requires us to update a row in a database table. Specifically, we need to change the…

Q: Create a query to add a column P to an existing view named view1 having columns (M,N,O) from the…

A: Create a MySQL query to add a column P to an existing view named view1 having columns (M, N, O) from…

Q: Q Which of the following code will remove all the rows from the table LOCATIONS?

A: Below Is the answer for above question with detailed explanation

Q: Need help with this Task 1 Create a table named REP. The table has the same structure as the…

A: As per Bartleby rules we can answer the first 3 questions at a time.So I have answered Task 1, 2 and…

Q: 16. Extract first column form "list1.txt" and redirect to "table1.txt" 17. Extract second and third…

A: In the first question, we need to extract the column and redirect the same thing in the table1.txt…

Q: Create a view named MONTHLY_RENTS. It consists of three columns: the first is the number of…

A: View in sql : view is a virtual table based on the result-set of an SQL statement.

Q: Which type of column can be simple or composite while also being correctly defined as both unique…

A: The question is asking about a type of column in a database table that can be simple or composite,…

Q: The DD_PLEDGE table contains the FIRSTPLEDGE column that indicates whether a pledge is the donor's…

A: Solution: Given: The DD_PLEDGE table contains the FIRSTPLEDGE column that indicates whether a pledge…

Q: For Film_ID 1, Rename the Title of the Film to INF 506 (if this gives an error, disregard it) For…

A: Answer is given below with proper explanation

Q: Italicize the contents of the range A4:A14 to separate the plant names from the rest of the data

A: In this question we have to understand how to change the font style of the contents from the range…

Q: Q1: Create the following related tables using appropriate data field specification: (Hint: Define…

A: CREATE - This command is used to create a new table in the database. ALTER- This command is used to…

Q: Mark as unused the Category column in the BOOK_PRICING table. Check that the column is no longer…

A: The BOOK_PRICING table is a part of a database used for managing pricing information for books. One…

Q: What are the guidelines to keep in mind when using a column alias?

A: The guidelines that must be kept in mind while using a column alias are as follows:

Q: use the code below to create tabels. include screenshots CREATE TABLE PRICE ( PRICE_CODE…

A: Usually, a command-line interface or database management tool is used to create tables.The steps to…

Q: What prevents a column from having a NULL value?

A: Introduction.The introduction of the question "What prevents a column from having a NULL value?" is…

Q: List all players for your Display the Team code and player number in a single column separated by a…

A: By changing the content in the table creation and information inserting by can achieve the solution:…

Q: To test weather a table has been modified correctly as you do these exercises, you can write and run…

A: 1. Write an INSERT statement that adds this row to the Terms table INSERT INTO Terms(terms_id,…

Q: Pages with porn should get the Porn flag even if the query is looking for porn results. a) True b)…

A: In the context of web search engines and content filtering, a 'Porn flag' is typically a marker or…

Concept explainers

Procedures in SQL

A pre-compiled stored procedure is a logical unit made up of one/more SQL statements (SQL stands for Structured Query Language). The database server saves it as an object. An SQL procedure is a collection of SQL statements and logic that is written and saved to accomplish a specified goal. An SQL procedure consists of a name, a parameter list, and a set of Transact-SQL statements. Stored procedures are stored as named objects in SQL Database Server.

Question

code of create table and insert table only please

Create the following table in your database with the following schema:
Table: Departments
I Column Name
| Type
I id
I name
int
| varchar I
+--
-----+
id is the primary key of this table.
The table has information about the id of each department of a university.
Table: Students
| Column Name
| Туре
I id
| name
| department_id | int
| int
I varchar I
+---
-----
id is the primary key of this table.
The table has information about the id of each student at a university and
the id of the department he/she studies at.
Add the following data to your tables:
Input:
Departments table:
+----
I id
I name
+----
| Electrical Engineering
| Computer Engineering
| Bussiness Administration |
| 1
| 13
+----
+----
Students table:
+------+-
| id
I name
| department id |
+-----
| Alice
| Bob
| Jennifer | 13
| John
| Jasmine
| Steve
| Luis
| Jonathan | 7
| Daiana
| Madelynn| 1
| 23
| 1
| 1
| 14
| 77
I 74
| 1
| 2
| 3
| 33
| 11
+------

Write an SQL query to find the id and the name of all students who are enrolled in departments that
no longer exist.
Return the result table in any order.
The result should be:
Output:
+----
| id
| name
+---
| John
| Daiana
| Jasmine
| Steve
| 2
| 7
| 3
+------
Explanation:
John, Daiana, Steve, and Jasmine are enrolled in departments 14, 33, 74, and 77 respectively.
department 14, 33, 74, and 77 do not exist in the Departments table.

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: Requirements

Step 2: query

Step 3: Screenshots

Step 4: output

Solution

Trending now

This is a popular solution!

Step by step

Solved in 5 steps with 4 images

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

Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field.
Which results would show if the artist table LEFT JOINed the album table? Only rows from the artist table that do not have related albums in the album table Only artists from the artist table that have albums All artists, even those with no albums in the album table All rows from the album table, even those that have NULL artist_id's
Which of the following DAX Functions will allow you to filter the data from a new column created from a summarize table? O Union Summarize O Calculatetables O Filter
The InstantRide User Satisfaction team requires the average and maximum number of rides users have taken so far with InstantRide. In addition, they would like to know the total number of travels. However, they need these details with the corresponding column names Average, Maximum and Total by using the AVG, MAX and SUM functions. In order to accomplish this, you will first need to create a derived table from the TRAVELS table to pass the TRAVEL_ID count to the three mathematical functions.
Create lookup functions to complete the summary section. In cell I6, create a formula using the VLOOKUP function to display the number of hours worked in the selected week. Look up the week number in cell I5 in the range A17:G20, and return the value in the 2nd column. Use absolute references for cell I5 and the range A17:G20.
Create a view named NHTrips. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in New Hampshire (NH). Display the data in the view
use the code below to create tabels. include screenshots of each completed table. CREATE TABLE PRICE ( PRICE_CODE NUMBER(2,0) PRIMARY KEY, PRICE_DESCRIPTION VARCHAR2(20) NOT NULL , PRICE_RENTFEE NUMBER(5,2) CHECK (PRICE_RENTFEE >= 0), PRICE_DAILYLATEFEE NUMBER(5,2) CHECK (PRICE_DAILYLATEFEE >= 0) ); CREATE TABLE MOVIE ( MOVIE_NUM NUMBER(8,0) PRIMARY KEY, MOVIE_TITLE VARCHAR2(75) NOT NULL, MOVIE_YEAR NUMBER(4,0) CHECK (MOVIE_YEAR > 1900), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR2(50), PRICE_CODE NUMBER(2,0) CONSTRAINT MOVIE_PRICE_CODE_FK REFERENCES PRICE ); CREATE TABLE MEMBERSHIP ( MEM_NUM NUMBER(8,0) PRIMARY KEY, MEM_FNAME VARCHAR2(30) NOT NULL, MEM_LNAME VARCHAR2(30) NOT NULL, MEM_STREET VARCHAR2(120), MEM_CITY VARCHAR2(50), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER(10,2) ); PRICE: INSERT INTO PRICE VALUES (1, 'Standard', 2, 1);…
The incomplete box plot and table show some information about some marks. Use the information in the table to complete the box plot. Use the information in the box plot to complete the table.
Select all rows from the Orders table where the CustomerID is 400 or 500.
Please help me with the syntax to create a table in Snowflake.
Insert a row into the LARGE_PROPERTY table for a new property. The office number is 1, The address is 2643 Lugsi Dr., The number of bedrooms is 3, The number of floors is 2, The monthly rent is $775, The owner number is MA111. Make sure to include the period (.) in the address.
Jump to level 1 Country ISOCode2 Name Code HU Hungary 348 DE BY Germany 276 Belarus 112 Complete the statement to delete the row containing 'Belarus'. In the WHERE clause, identify the row to be deleted using the ISOCode2 column. DELETE FROM Country WHERE