EBK DATABASE CONCEPTS
7th Edition
ISBN: 9780133777840
Author: AUER
Publisher: VST
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 3, Problem 3.47RQ
Program Plan Intro
SQL:
- SQL stands for “Structured Query Language”.
- The current version of SQL is “ANSI SQL – 92”.
- It contains constructs which are used to define and process
database . They are executed using DBMS-supplied command prompt. - SQL is not a
programming language. It is text-based and it is also called as data sublanguage. In order to get SQL as a complete programming language, it should be included in scripting languages like Java, C#, and so on.
ALTER TABLE Statement:
“ALTER TABLE” statement comes under Data Definition Language. It is used to modify column, add constraints, add column(s), drop column, rename column name and table name, drop constraints, and so on. To add a new column, “ADD COLUMN” keyword should be used after mentioning the table name.
Syntax:
Syntax to add a column to an existing table is as follows: (The below query was executed in SQL Server Management Studio).
ALTER TABLE table_Name ADD COLUMN column_Name datatype;
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Write a PL/SQL Function that accepts 4 integers, 2 representing the homeTeamID and visitorTeamID, and the other 2 representing the score for each team during a game : htscore and vtscore
The function will determine which team won the match and return the teamID of the winning team. You can achive this by comparing htscore > or < then vtscore.. if htscore is bigger then return hteamID or else return vteamID.
If the game was a tie (the 2 scores are the same value) return a 0.
//trying to make recursive SQL statement to get the days of the week , can you tell me what does not work in this query:
WITH cte_number (counter, weekday) AS(SELECT 0, Datename(DW,0) UNION ALLSELECTcounter+1, Datename(DW, counter+1)FROM cte_numberwhere counter<6) select weekday from cte_number;
//thanks for you assistance
Write the SQL statements using the university schema to perform the followingoperations: Delete all takes tuples corresponding to any section of any course withthe word “advanced” as a part of the title; ignore case when matching theword with the title.
Chapter 3 Solutions
EBK DATABASE CONCEPTS
Ch. 3 - Prob. 3.1RQCh. 3 - What is a data sublanguage?Ch. 3 - Prob. 3.3RQCh. 3 - Prob. 3.4RQCh. 3 - Prob. 3.5RQCh. 3 - Why do some standard SQL-92 statements fail to run...Ch. 3 - Use the following tables for your answers to...Ch. 3 - Write an SQL CREATE TABLE statement to create the...Ch. 3 - Prob. 3.9RQCh. 3 - Prob. 3.10RQ
Ch. 3 - Prob. 3.11RQCh. 3 - Is PET or PET_2 a better design? Explain your...Ch. 3 - Prob. 3.13RQCh. 3 - Prob. 3.14RQCh. 3 - Prob. 3.15RQCh. 3 - Prob. 3.16RQCh. 3 - Prob. 3.17RQCh. 3 - Write an SQL statement to display the breed, type,...Ch. 3 - Prob. 3.19RQCh. 3 - Prob. 3.20RQCh. 3 - Write an SQL statement to display the breed, type,...Ch. 3 - Write an SQL statement to display the name, breed,...Ch. 3 - Prob. 3.23RQCh. 3 - Prob. 3.24RQCh. 3 - Prob. 3.25RQCh. 3 - Write an SQL statement to display the name and...Ch. 3 - Prob. 3.27RQCh. 3 - Write an SQL statement to count the number of...Ch. 3 - Write an SQL statement to count the number of...Ch. 3 - Prob. 3.30RQCh. 3 - Prob. 3.31RQCh. 3 - Prob. 3.32RQCh. 3 - Prob. 3.33RQCh. 3 - Answer question 3.33, but do not consider any pet...Ch. 3 - Write an SQL statement to display the last name,...Ch. 3 - Write an SQL statement to display the last name,...Ch. 3 - Write SQL statements to (1) create the BREED...Ch. 3 - Prob. 3.38RQCh. 3 - Prob. 3.39RQCh. 3 - Prob. 3.40RQCh. 3 - Write an SQL statement to display the...Ch. 3 - Write SQL statements to add three new rows to the...Ch. 3 - Write SQL statements to add three new rows to the...Ch. 3 - Prob. 3.44RQCh. 3 - Explain what will happen if you leave the WHERE...Ch. 3 - Prob. 3.46RQCh. 3 - Prob. 3.47RQCh. 3 - Prob. 3.48RQ
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
- Develop a PL/SQL block that would go through all the employees from EMPLOYEES table who work in department id= 90 (there is suppose to be 3 employee who work in Department 90)- IN BEGIN END END SECTION; Execute 3 separate of SELECT statement one after the other. Each SELECT will pull first name. salary of each employee and store them in local variables.+ SELECT LOAD INTO v_firstname1, v_salarxlWHERE emrlexee.id=100 Then second WHERE emploxee.id3101+ SELECT first_name, salary INTO v_firstname2, v_salary2 Then third + SELECTSELECTI tirst.name, salary INTO v_firstname3, v_salary3 iin WHERE emploxee id=102 ..... You must now have 6 local variables. DECLARE all of them accordingly in DECLARE section and use them in BEGIN and END.+ Then add another SELECT statement in BEGIN-END to find company average salary SELECT xa(salary) INTO aKasal FROM EMPLOYEES; !!! Do not forget to create y avgsal local variable in DECLARE section.arrow_forwardExplain what the following SQL statements do. SELECT MAX(NewColumn) FROM (SELECT Patientid, COUNT(*) AS 'NewColumn' FROM PhysicianDX GROUP BY Patientid) AS CountTablearrow_forwardFor 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 femalearrow_forward
- You have the following tables: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) ACCOUNTS (ACCOUNT_NUMBER, MANAGER_ID, CUST_ID, BALANCE) CUSTOMERS (CUST_ID, LAST_NAME, FIRST_NAME) Write a SQL statement that lists account number, balance, MANAGER’s last name, CUSTOMER ID, and CUSTOMER’s last name for every account in the ACCOUNTS table.arrow_forwardWrite a SQL statement to get the list of all the law firms from thedatabase in alphabetical order by firm name. Include all thecolumns from the LawFirm table.Write a SQL statement to get the list of all the law firms with aname that contains the letter Z. Include all fields from the LawFirmtable.Write a SQL statement to get the list of all the lawyers, showingonly the name and surname of the lawyer, and the name of theirfirm. What is the difference between the WHERE and HAVING clauses in SQLstatements? What is the purpose of an index in a SQL database?arrow_forwardImplement 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.…arrow_forward
- Develop a PL/SQL block that would go through all the employees from EMPLOYEES table who work in department_id= 90 (there is suppose to be 3 employee who work in Department 90) IN BEGIN END END SECTION; Execute 3 separate of SELECT statement one after the other. Each SELECT will pull first_name , salary of each employee and store them in local variables. SELECT LOAD INTO v_firstname1, v_salary1 WHERE employee_id=100 Then second SELECT first_name, salary INTO v_firstname2, v_salary2 ….. WHERE employee_id=101 Then third SELECT, SELECT first_name, salary INTO v_firstname3, v_salary3 ….. WHERE employee_id=102 You must now have 6 local variables. DECLARE all of them accordingly in DECLARE section and use them in BEGIN and END. Then add another SELECT statement in BEGIN-END to find company average salary SELECT avg(salary) INTO v_avgsal FROM EMPLOYEES; !!! Do not forget to create v_avgsal local variable in DECLARE section.arrow_forwardThe Midnight Coffee Roastery is running a special on decaf coffee. Write an SQL statement that changes the price of all decaf coffees to 4.95.arrow_forwardQ2: 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.arrow_forward
- By using this table answer this question: Write ONE SQL statement which uses the SUBSTR function (as many times as you need to) to extract the following three words: ‘I’, ‘am’, ‘here’ from the string ‘Where am I?’ and put the three words together using a combination of the CONCAT function and the ||. E.g. The starting string used in the SQL statement is: ‘Where am I?’ The output of the SQL statement after we run it will be: TRANS ---------- I am herearrow_forwardQuestion 7 Writes a PL/SQL function that calculates the factorial of a number passed as a parameter. The Factorial of a number n is defined as - O! = 1 n! = n*(n-1)*(n-2)*(n-3)... 1 for n>0arrow_forwardWrite an SQL statement to change the value of Std. Poodle in BreedName of PET_3 to Poodle, Stdarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education