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.
Grouping Rows:
SQL contains “GROUP BY” clause in order to group rows by common data. Though it is very powerful feature, it is hard to understand.
Syntax:
SELECT column_Name1 FROM table_Name GROUP BY column_Name2;
Example: Consider a table “student” contains two columns “student_Name” and “Department”. “GROUP BY” clause is used when there is a need to get the number of students from each department. The query for this scenario is given as follows.
SELECT department, COUNT (department) FROM student GROUP BY department;
When the above query is executed, number of students from each department will be displayed.
Want to see the full answer?
Check out a sample textbook solutionChapter 7 Solutions
Database Systems: Design, Implementation, & Management
- Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by the employee’s last name. The results of running that query are shown in Figure P7.3.arrow_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_forwardUsing the information in the ASSIGNMENT table, write SQL code that will return the total number of hours worked by each employee and the total cost of those hours, sorted by employee number.arrow_forward
- By using this table answer this question in SQL : Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number and department name should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.arrow_forwardthis is SQL.arrow_forwardWrite SQL code that will return the total number of hours worked for each employee and the total costs resulting from those hours worked, sorted by employee number, using the data in the ASSIGNMENT table.arrow_forward
- Write SQL code that returns the EMP_NUM and Number of ratings he/she earned (based on EARNEDRATING table, call this Num_RTG), for those who had at least got 1 rating earned, but the number of ratings he/she earned must lower than the average number of ratings for all employees who had earned ratings before, and such employee has been served as “Copilot” before.arrow_forwardTable: Employee | Column Name| Type| | id | int | | int | | salary +- -------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee. Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.arrow_forwardUSING SQL Using the Henry Books database, for every author listed in the author table, list each book written and sold at Henry Books, if any. Display the author number as ‘Author Number’, concatenate the author name as first name last name as ‘Author Name’, and book code as ‘Book Code’. Order by author number in ascending order. Be sure all rows are displayed.arrow_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_forward8) Display the firstname, lastname of the employees and the customerID that the employee served such that the employees are born after '1960-01-01'. Arrange the results by the last name. 6) Write a SQL query to output the product name and the quantity of the Orders of that product for those orders with more than 70 quantity. (hint: don’t have to use GROUP BY) NOTE: Please use only table joining to write the SQL quereys.arrow_forwardWrite the SQL code to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number.arrow_forward
- Programming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher: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 LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr