Select the jobs below that may use SQL in their work (select all that apply)

Question

This query must first create a nested table expression (DINFO) to get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column that is used in the WHERE clause.

 SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT
  FROM EMPLOYEE THIS_EMP,
        (SELECT OTHERS.WORKDEPT AS DEPTNO,
                           AVG(OTHERS.SALARY) AS AVGSALARY,
                           COUNT(*) AS EMPCOUNT
          FROM EMPLOYEE OTHERS
          GROUP BY OTHERS.WORKDEPT
         ) AS DINFO
  WHERE THIS_EMP.JOB = 'SALESREP'
   AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

Nội dung chính Show

SELECT statements
Learning more about SQL
How does a data scientist and DBA differ in how they use SQL?
Which of the following statements are true of entity relationship diagrams?
What is true about ER modeling?
Which of the following basic elements of an ER diagram is named using singular nouns?

Using a nested table expression for this case saves the processing resources of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided. Because of the context of the rest of the query, only the rows for the department of the sales representatives are considered by the view.

Example 9 - Display the average education level and salary for five random groups of employees.

This query requires the use of a nested table expression to set a random value for each employee so that it can later be used in the GROUP BY clause.

  SELECT RANDID , AVG(EDLEVEL), AVG(SALARY)
    FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID
             FROM EMPLOYEE
         ) AS EMPRAND
    GROUP BY RANDID

In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.

SELECT statements

An SQL

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 statement retrieves records from a database table according to clauses (for example,

  SELECT * FROM Customers WHERE Last_Name='Smith';

1 and

  SELECT * FROM Customers WHERE Last_Name='Smith';

2) that specify criteria. The syntax is:

  SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In the above SQL statement:

The
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
0 clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
4 (an asterisk).
The
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
1 clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
The
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
2 clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example,
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
7).
The semicolon (
```
  SELECT * FROM Customers WHERE Last_Name='Smith';
```
8) is the statement terminator. Technically, if you're sending only one statement to the back end, you don't need the statement terminator; if you're sending more than one, you need it. It's best practice to include it.

Note:

SQL is not case sensitive (for example,

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 is the same as

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

0). For better readability, some programmers use uppercase for commands and clauses, and lowercase for everything else.

Examples

Following are examples of SQL

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 statements:

To select all columns from a table (

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

2) for rows where the

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

3 column has

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

4 for its value, you would send this

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 statement to the server back end:

  SELECT * FROM Customers WHERE Last_Name='Smith';

The server back end would reply with a result set similar to this:

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

To return only the

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

6 and

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

7 columns, based on the same criteria as above, use this statement:

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

The subsequent result set might look like:

  +---------+------------+
  | Cust_No | First_Name |
  +---------+------------+
  | 1001    | John       |
  | 2039    | David      |
  | 2098    | Matthew    |
  +---------+------------+
  3 rows in set (0.05 sec)

To make a

  SELECT * FROM Customers WHERE Last_Name='Smith';

2 clause find inexact matches, add the pattern-matching operator

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

9. The

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

9 operator uses the

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

1 (percent symbol) wild card to match zero or more characters, and the underscore (

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

2) wild card to match exactly one character. For example:

To select the

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

7 and

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

4 columns from the

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

5 table for rows in which the

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

4 column contains the string "brain", use this statement:

  SELECT First_Name, Nickname FROM Friends WHERE Nickname LIKE '%brain%';

The subsequent result set might look like:

  +------------+------------+
  | First_Name | Nickname   |
  +------------+------------+
  | Ben        | Brainiac   |
  | Glen       | Peabrain   |  
  | Steven     | Nobrainer  |
  +------------+------------+
  3 rows in set (0.03 sec)

To query the same table, retrieving all columns for rows in which the

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

7 column's value begins with any letter and ends with "en", use this statement:

  SELECT * FROM Friends WHERE First_Name LIKE '_en';

The result set might look like:

  +------------+------------+-----------+
  | First_Name | Last_Name  | Nickname  |
  +------------+------------+-----------+
  | Ben        | Smith      | Brainiac  |
  | Jen        | Peters     | Sweetpea  |  
  +------------+------------+-----------+
  2 rows in set (0.03 sec)

If you used the

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

1 wild card instead (for example,

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

9) in the example above, the result set might look like:

  +------------+------------+-----------+
  | First_Name | Last_Name  | Nickname  |
  +------------+------------+-----------+
  | Ben        | Smith      | Brainiac  |
  | Glen       | Jones      | Peabrain  |
  | Jen        | Peters     | Sweetpea  |
  | Steven     | Griffin    | Nobrainer |  
  +------------+------------+-----------+
  4 rows in set (0.05 sec)

Learning more about SQL

To learn more about SQL programming, Indiana University students, faculty, and staff can download materials for self-study from IT Training.

For the general public, various online tutorials are available, such as the w3schools.com SQL Tutorial.

How does a data scientist and DBA differ in how they use SQL?

How does a data scientist and DBA differ in how they use SQL? DBAs manage the database for other users. Data scientists don't write complex queries. Data scientists only query the database and don't create tables.

Which of the following statements are true of entity relationship diagrams?

Which of the following statements are true of Entity Relationship (ER) Diagrams? They are usually a representation of a business process. They show you the relationships between tables. They usually are represented in a visual format.

What is true about ER modeling?

Entity Relationship Model (ER Modeling) is a graphical approach to database design. It is a high-level data model that defines data elements and their relationship for a specified software system. An ER model is used to represent real-world objects.

Which of the following basic elements of an ER diagram is named using singular nouns?

Entities are represented in ER diagrams by a rectangle and named using singular nouns.

Weak relationship Entity relationship model SQLite example

Select the jobs below that may use SQL in their work (select all that apply)

SELECT statements

Examples

Learning more about SQL

How does a data scientist and DBA differ in how they use SQL?

Which of the following statements are true of entity relationship diagrams?

What is true about ER modeling?

Which of the following basic elements of an ER diagram is named using singular nouns?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội