Concept explainers
WHERE Statement:
“WHERE” statement is used limit the number of rows. For example: Consider a table “FTable” that has two columns named “FruitName” and “Color”. “WHERE” clause is used when there is a need to display the entire FruitName whose color is Red.
SELECT * FROM FTable WHERE color = 'red';
When the above statement is executed, red colored fruits get displayed.
ORDER BY Clause:
SQL contains “GROUP BY” clause in order to sort rows. The values get sorted in ascending as well as descending order. The keyword used to sort values in ascending order is “ASC” and for descending order is “DESC”. By default, it sorts values by ascending order.
Syntax:
SELECT column_Name1, column_Name2 FROM table_Name ORDER BY column_Name2;
Want to see the full answer?
Check out a sample textbook solutionChapter 7 Solutions
Database Systems: Design, Implementation, & Management
- Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.arrow_forwardReview the data for the TRIP table in Figure 1-5 in Chapter 1 and then review the data types used to create the TRIP table in Figure 3-39. Suggest alternate data types for at least two fields that currently have the CHAR data type and at least two fields that currently have the DECIMAL data type. Create but do not execute the SQL command to create the TRIP table using these alternate data types.arrow_forwardConsidering Vaccination Database, write queries to: Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below: CNIC Name Contact Dose1 Center Dose2 Center 22401-6645321-1 Nasir 3409991112 Lachi Kohat 2 14301-6045321-5 Shahab 3409991112 kohat 3 Dara The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single dayarrow_forward
- Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7–15, the EMP_2 table will contain the data shown in Figure P8.15.arrow_forwardThisarrow_forwardIn sql get to know the HR database, its tables, and their contents. Show the SQL query and the output. Display job Title, the difference between minimum andmaximum salaries for jobs with max salary in the range10000 to 20000 Display employees where the first name or last name startswith S.arrow_forward
- Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows:A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an inputparameter ‘region_id’, and print the name of the region with that id. Then execute this function withregion_id = 1arrow_forwardUse this info to answer the questionTABLE NAME: students COLUMNS:student-no NUMBER(6) fname VARCHAR2(12) lname VARCHAR(20) sex CHAR(1)major VARCHAR2(24) 7. Write a SQL statement that will display the student number(studentno),firstname(fname),and last name (lname) for all students who are female (F) in the table named students.arrow_forwardthis is SQL.arrow_forward
- Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number. The results of running that query are shown in Figure P7.4. PROJ_NUM 15 18 22 25arrow_forwardPlease help with the following: Write following SQL Queries:A simple database composed of the following tables: PATIENT, DOCTOR, DRUG, and PRESCRIPTION, List all the rows in which the prescription dates occur on or after November 14. How many doctors provide prescriptions with 30 tablets in total? List doctor’s details where speciality is either Dermatology or Neurology. (Use INoperator). List the results by the first name in ascending order and the last name indescending order Find all patients rows whose first names include ‘George’ (Assume case sensitivity). List number of patients that do not have a phone number. How many drugs with price < 40$? How many prescriptions are generated for each patient, limit the listing to patients who have more than 2 prescriptions? List the drug codes and prices (rounded to one decimal place). How many doctors prescribe the drug code KO15? List all drugs with their expiration date (one year from the date of manufacturer)…arrow_forwardWrite PL/SQL procedure to display the name of all employees in the department which has Department_ID = 99 then display the total number of employees in this department. Note: you must use CURSOR for this question. You can use the following DBMS output statement : dbms_output.put_line('employee name '||X ); which means display ‘employee name’ together with the value of the variable Xarrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage