Subject: Database Write the SQL statements for following modification tasks: a) Add constraint to the table BORROWER named borrower_cardno_pk to create the attribute name as the primary key. b) Delete the column city from the table BORROWER c) Modify the column of "phone" in table BORROWER, change the data type into type of VARCHAR2 with length of 20.
Q: select * from cast where cast_id=162652763; Use explain to display the query plan without creating…
A: the answer is given below
Q: Write a query to fetch employee names and salary records. Display the employee details even if the…
A: Given: Write a query to fetch employee names and salary records. Display the employee details even…
Q: PLZ make the following work sql Write a query to display the membership number, first name, last…
A: SELECT b.MEM_NUM, a.MEM_FNAME, a.MEM_LNAME,a.MEM_BALANCE,RENT_DATEFROM MEMBERSHIP a, RENTAL b where…
Q: 2) Create in PL/SQL a stored procedure called 'Del Details' to delete the record of orderedProduct…
A: Answer is given below-
Q: Write a query that shows the make, model, VIN number and year of manufacture only for cars that have…
A: i will give this question answer in next step
Q: Write a query to display the guest names who are from Ooty. For instance, the guest name "geeta…
A:
Q: Write a query to generate a report indicating the total number of sick leave days for each employee.…
A: Actually, database is a used to stores the data\information.
Q: Write a query to create a report that displays the count of the number of tables in the SQ library.…
A: The question is to write SQL statement for the given query.
Q: 6. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query…
A: The first query should validate the the ASSIGN_CHARGE values in the ASSIGNMENT table. ASSIGN_CHARGE…
Q: a. Find all movies with a cost less than or equal to the average movies cost b. Write a query to…
A: Below is the correct code for the SQL query to enter the values of DATE in respective tables. Kindly…
Q: Write a query to display the guest names who are from Ooty. For instance, the guest name "geeta…
A: Question Breakdown: According to the problem statement provided we should upgrade the SQL query to…
Q: Write stored procedure in SQL Find all employees whose salary is larger than the salary of at least…
A: Answer: I have done code and also I have attached code as well as code screenshot.
Q: Write a query to display the guest names who are from Ooty. For instance, the guest name "geeta…
A: Given:
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: 23.Write a SQL query to add constraint PRIMARY KEY to the column Pro_ ID with a suitable constraint…
A: A Primary key is a set of attributes that can identify each tuple uniquely of the given relation. It…
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: address Write a query to display the guest names who are from Ooty. For instance, the guest name…
A: Required: We have to write a SQL query that displays the guest names who are from Ooty and the…
Q: Create a SQL query that selects OrderID and OrderDate from the Orders database and OrderDetailID…
A: Definition A JOIN clause is used to join data from more than "2" tables together based on a common…
Q: A subquery must be placed in the outer query’s HAVING clause if: (refer to the tables in the JustLee…
A: Answer: The correct option is “(b) The value returned by the inner query is to be compared to…
Q: Write an SQL query using the ALTER TABLE and ADD operator to add anattribute named ORDERDATE with…
A: ALTER TABLE command: The ALTER TABLE command is used to add new columns, modify columns in an…
Q: Write a query to verify that the data has been entered correctly.
A: First, insert data into the table using a query for example, declare @firstName varchar(500) =…
Q: Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: )…
A: Below is the required procedure:
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: Based on table below, using oracle: used subqueries,display the name of supplier's that supplied…
A: To Fetch more expensive computer than that being supplied by IBM we need to use the subqueries and…
Q: 1. Write a query to create a table employee with Emp No (4), Ename, Department, and Salary and…
A: Given Data :
Q: The given SQL creates a Movie table and inserts some movies. The SELECT statement selects all…
A: Answer
Q: How do you specify conditions for an Access query?
A: How do you specify conditions for an Access query?
Q: Modify the query in Problem 13 to include the number of individual product purchases made by each…
A: Actually, database is a used to stores the and also manipulate, change and modify the data.
Q: Write a query to display the author ID, first and last name for all authors that have never written…
A: From a database, required information can be retrieved by the query language. The queries must be in…
Q: I-Show all data from employee table where name should be in ascending and salary in descending…
A: Note: The answer of I and III questions is shown below. Please repost the question with the details…
Q: Write a query to display the patron ID, book number, and days kept for each checkout. “Days Kept” is…
A: below are the two commands that you can try: 1. Select PATRON.PAT_ID, BOOK.BOOK_NUM,…
Q: O b. Natural joins return only matched columns in the result set.
A: We need to solve:
Q: Write the SQL code required to list the employee number, last name, first name, and middle initial…
A: SELECT is the keyword used in the SQL to fetch columns which are required to be displayed in the…
Q: This clause in SQL join is based on all columns which have the same names, but the data types do not…
A: b. USING clause
Q: Based on table below, using oracle. used subqueries,display the name of supplier's that supplied…
A: Check for the supplier id's who supplied whose value is more than that of IBM computers Then, you…
Q: Write a query to show all record of job-history of those employees whose department number 60?…
A: Find the required query and output given as below :
Q: Write a query to display the guest names who are from Ooty. For instance, the guest name "geeta…
A: As per the given problem we have to develop a SQL query to display the guest names who are from…
Q: d. write a query to retrieve the name of graduate student and student advisor name of all graduate…
A: The query works for both ms sql server and mysql.
Q: d. Create a NewMember table with only using the membership name (Fname and Lname) and membership…
A: Create tables and insert data into it. Subqueries allow you to write queries that pick data rows…
Q: Write a query to generate a report indicating the total number of sick leave days for each employee.…
A: As you have asked multiple questions, so we will solve first question for you. If you want any…
Q: Write queries in SQL to answer each of the following questions: 1. Find all students in Comp.…
A: SQL is a standard language for storing, manipulating and retrieving data in databases.
Q: To obtain the structure of an Oracle table, the command to use is: a) STRUCTURE [TableName]. b)…
A: Dear Student, DESCRIBE is used in Oracle to obtain the structure. STRUCTURE , DESCRIBE STRUCTURE,…
Q: Create a query for displaying first_name of employees whose Initial is 'A' using instr() function.
A: Method 1 using Instr() function :- SELECT first_name, INSTR(first_name, "a") FROM tablename ;
Q: QUESTION 4 constraint may be used to place a restriction on a column to ensure that no duplicate…
A: NO duplicate value means all the values in the column are different
Q: Write query "Display a list of all data contained in the BOOKS table"
A:
Q: Consider the following table: Instructor (Instructor_Id, Instructor_Name, specialization, Address,…
A: The syntax for writing the PL/SQL function is- CREATE [OR REPLACE] FUNCTION function_name…
Q: 31) Using functions display: a) sum of all salaries b) average of all salaries c) highest salary…
A: As per our company guidelines we are supposed to answer only one question. Kindly repost other…
Q: b. Write a query to get the average salary and the total number of employees who work in the History…
A: Functions used: The COUNT() function returns the number of rows. The AND operator displays a record…
Subject:
Write the SQL statements for following modification tasks:
a) Add constraint to the table BORROWER named borrower_cardno_pk to create the attribute name as the primary key.
b) Delete the column city from the table BORROWER
c) Modify the column of "phone" in table BORROWER, change the data type into type of VARCHAR2 with length of 20.
Step by step
Solved in 2 steps
- //Need typed version please You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project. The schema for the database used in this question is as follows: ( primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City) PART (PNo, PartName, Weight) PROJECT (JobNo, JobName, StartYear, Country) SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc or the words ‘PROJECT’, ‘RESTRICT’ etc . do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the name of any part that was not used on a project that commenced in 2020. List the name of any part that has been supplied to all projects that commenced in 2020.Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,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…
- The properties of the relation object can be used to implement the master-detail functionality which most appropriate for your application. You can delete a master-detail relation by selecting it in the Object Navigator and choosing Navigator => Delete. Discuss the Deletion of Relations in detail.STORY BOOK # Id * Name * Published date o Publisher * Type Find the error in the given ERD written by write O g. Novel name is multivalued attribute Oh. Type attribute must be in AUTHOR side O i. Cardinalities of relationships are not correct AUTHOR # Id * Name * Surname o Age O a. There is a volatile attribute and it must be converted to nonvolatile attribute O b. "Published date" and "Publisher" attributes are violating 3NF O c. Name attribute cannot be used in both entities since it is not allowed to have same name O d. Name of the "STORY BOOK" must be written as "STORY_BOOK" O e. Optionality of relationship in AUTHOR side cannot be mandatory O f. "Surname" attribute can be optional# Question 1#Using the World database, in the city table, insert a new city for the Tasmania district. The city name is ‘Devonport’, #country code is ‘AUS’, district is ‘Tasmania’ and population is 25,387. Remember, the ID is set to auto-increment #and you will not assign a value. You will insert 2 snips for this question: #a. Insert a snip of the construct used. This MUST be a snip of the code used to insert this city into the city table. #b. Run a query to display the entire new row of Devonport inserted in the City table. Insert your snip here:
- The following relations kind a part of a on-line database (the relations area unit self-explanatory): CUSTOMER/CLIENT/SHOPPERS/BUYERS(Cust#, CAddress, Ctown, Balance,Cname) ORDER(Order.#, Date, Cust#.) ORDERLINE(Order#, Part#., Amount-Ordered) PART(Part#., Desc, Stock, Item-Class, Unit -Price) Design a physical ER for the above scenario. With respect to this relative schema, write SQL statements for the subsequent queries: List the name of the client United Nations agency has placed Order 123 (i.e. Order: = 123). List the names of shoppers United Nations agency in Cardiff and have ordered half 456 (i.e. Part. 456). List the order numbers (Order) for orders that contain a minimum of three elements and also the quantity ordered are minimum of ten. List the outline (Desc), of these elements that the typical quantity ordered is bigger than the number ordered for half .6 (Part. = 456) so as 123.(Order. .123).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 IDs11. Foreign key is the one in which the ________ of one relation is referenced in another relation. a. Foreign key b. Primary key c. References d. Check constraint
- 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.Type ISBN eBook RelationName2 (AttrName1) Primary key {AttrName1) Price Book N Order Hardcopy Date Quantity RelationName1 (AttrName1, AttrName2) Primary key {AttrName1} Foreign key {AttrName2} references RelationName2 (AttrName1) M CoverStyle Customer Id Date Name Has Convert the following E-R diagram to a collection of relations. Determine all the primary and foreign keys. Use the following format to answer the question: Payment NoA relation that has no transitive dependencies is in __________ normal form.