Consider a database, containing a table called info. The table info contains 4 columns first name (of type text), last.name (of type text), birth_year (of type integer), and occupation (of type text). You may assume that the table info is not empty and that all entries are properly entered so that the following questions can be answered. Give the SQL command to a) sort the entries according to the year of birth in ascending order b) return all entries of persons that work as a "pilot" c) return all entries of persons that are were born before 1990 d) add person John Doe, born in 1985, and works as a cook to table info

Consider a database, containing a table called info. The table info contains 4 columns first name (of type text), last.name (of type text), birth_year (of type integer), and occupation (of type text). You may assume that the table info is not empty and that all entries are properly entered so that the following questions can be answered. Give the SQL command to a) sort the entries according to the year of birth in ascending order b) return all entries of persons that work as a "pilot" c) return all entries of persons that are were born before 1990 d) add person John Doe, born in 1985, and works as a cook to table info

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

Similar questions

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),…
There are three tables in this database (see image). Write MYSQL code for: (1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired after a row is inserted in table “Inventory”. After a row is inserted in table “inventory”, the “itemid”, the insertion time, and the action is inserted in table “Inventory_history”. The action is set to ‘add an item’. The oldprice is set to null. Test your trigger by inserting a row into Inventory and displaying the contents of Inventory_history. (2) Create a trigger “change_quantity” on table “Transaction”. The trigger is fired after a row is inserted in table “Transaction”. After a row is inserted in table “Transaction”, update the “quantity” in table “Inventory”. For example, if 3 iWatch are sold, then the quantity of iWatch in table “Inventory” is decreased by 3. Test your trigger by inserting a row into Transaction and displaying the contents of the relevant row in Inventory. (3) Create a trigger “change_price” on table…
Implement the following ER model and sample data using Oracle SQL Developer and your CDM Oracle database account. You will submit a SQL script file and its entire outputs. The table name should be STUDENTS (plural). Each StudentID should be a five-digit number. LastName and FirstName must be entered. 12341, Doe, Jane 12342, [last name of your choice], [first name of your choice] 12343, [last name of your choice], [first name of your choice] Enrollment The table name should be ENROLLMENTS (plural). Quarters should be something like 2021Fall, 2022Winter, 2022Spring, 2022Summer1, 2022Summer2, 2022Summer10, and so forth. Grades in future quarters are entered as null. 2021Fall, 12341, IS411, A 2022Winter, 12342, IS421, null 2022Summer10, 12343, [course number of your choice], null Course The table name should be COURSES (plural). CourseNumbers should be strings of up to seven characters (e.g., IS411, DSC411, CSEC440). The course and instructor names should be strings of characters.…
detection. Exercise 1: Addresses in Bahrain are identified using three numbers separated using commas and one single blank space. The three numbers are the house number, road number, and the block number respectively. Assume that the three numbers can have I to 4 digits. An example of a valid address is: '32, 1024, 5240'. Write a PL/SQL program that extracts the three components of the address for further processing. A sample of the output is as follows: The house number is: 32 The road number is: 1024 The block number is: 5240 Exercise 2: Consider the following student table Student (S ID, S_fname, S_Iname, S_DOB, F_ID). Write a PL/SQL program that 1. Extracts the year from the student date of birth, 2. Checks whether the year is a leap year, then 3. prints the student's first name, last name and whether he was born in a leap year. A sample of the output is as follows: Mohammed Ali was born in a leap year Mohammed Ali was not born in a leap year //if the year of birth is not leap //if…
PATHS is a table that contains information about paths on a number line. The structure of PATHS is as follows, where x1 and x2 represent that there is a path from Coordinate x1 to Coordinate x2 (You can't move from Coordinate x2 to Coordinate x1 ). NAME ΤΥΡE NULLABLE X1 INT FALSE X2 INT FALSE Problem Please write an SQL statement that returns the beginning and end point of each path in PATHS . Sort them by the value of the beginning point in ascending order. Constraints • In the case where a direct path from Coordinate a to Coordinate b is available, no path from Coordinate b to Coordinate a will be given. |x1-x2| = 1 • No path will overlap with one another. Example Suppose that PATHS is as follows: x1 x2 1 2 2 3 4 7 7 6 This table can be visualized as follows: START END END START 7 8. 9 Therefore, your SQL statement must return the following: start end 1 4 8 6
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.
Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions. Under what circumstances would you create a View?
You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. The state names are abbreviated. You want to check for state names that are greater than 2 characters long. You write the SQL query below. Add a LENGTH function that will return any state names that are greater than 2 characters long.
There's a "friend_requests" table that has a column "action_taken" that displays if the friend request was rejected, requested, or accepted.Implement a SQL query to return the percentage of "action_taken" that were "accepted".
SQL Use appropriate clauses and filters to run the query with minimum time complexity. 2. Try to avoid running the queries on unnecessary rows and columns. 3. Share the queries along with a brief description of the code in a python file, so that the results are reproducible. Q1: Write a query to find the students working in a company having more ratings than their seniors EMPLOYEES RATING TABLE student id student name rating senior id designation 1 george 9 3 student 2 micheal 8 4 student 3 mary 6 null senior 4 steve 9 null senior For the above table GEORGE has more rating than his sENIOR
Update this database with the following stored modules and submit the updatedCompanyDB.sql file as your solution. Additionally, provide screenshots for all the code 1. A stored procedure that takes in a department identifier and prints a list of all employeesin that department.2. A stored procedure that lists all employee names that do not have any dependents.3. A function that takes in an employee id and returns the number of hours the employee isworking on all their assigned projects4. A stored procedure that updates the ‘Hours’ field for all employees in the employee tableusing a cursor and the function defined in part 3.5. Triggers that are activated after any inserts, deletes, or updates on the WORKS_ON table.The triggers will update the Hours field in the Employee table to reflect any changes forthe hours worked.6. A stored procedure that lists any employees working less than 20 hours.
For the following database scheme Employee(empNo,fName.IName.address.DOB.sex,position.deptNo) Department(deptNo.deptName,mgrEmpNo) Project(projNo.projName,deptNo) WorksOn(empNo.projNo.dateWorked,hoursWorked) Write an SQL statement to find all the details of employees who are female