TERADATA SQL Learning Essay < a >

Recently learned SQL in Teradata environment. Here to record the study of the knowledge points, as a record.

Directory:

1. About SQL learning and the online database used
2. Table Union (join)
3. SQL Sub-query
4. Create a new field at select (as, Case time)
5. Data Grouping (group BY + aggregate function count, SUM, AVG, etc.)
6. Use over (partition by) to group data and create new fields
7. Sample Selection

1. About SQL learning and the online database used

I have read some books on SQL learning before. But if from the learning efficiency, follow the book to learn SQL, rather than directly look at the production environment of the work Code, encountered the statement does not understand when the search engine to find the corresponding statement, the efficiency will be higher (for example, this is the focus of this kind of embodiment).

Of course, in addition to sporadic knowledge points, there are some online practice SQL fetch number of Web sites, listed here are as follows:

1) Basic SQL test: Http://www.w3school.com.cn/quiz/quiz.asp?quiz=sql

http://sqlzoo.net/

2) Basic Grammar Practice-codecamdy:https://www.codecademy.com/zh/learn/learn-sql

3) Online Direct Connect database practice http://www.sqlcourse.com

Https://livesql.oracle.com/apex/livesql/file/index.html

http://www.sql-ex.ru/

Of course, we can also go to some basic practice database, install MySQL and so on to practice. But general introduction, learning grammar is more important than learning how to install a database. It's a matter of opinion.

In addition, it is more important to optimize the efficiency of SQL statement, how to understand database structure and so on, compared with the simple and understandable SQL syntax learning. This is also the next step of my study focus. Specifically refer to this link https://www.zhihu.com/question/20116482

So next, I'll use the Oracle database to practice the most recently consulted knowledge points.

after registering https://livesql.oracle.com/apex/livesql/file/index.html on this website, select "EMP and DEPT " in the Code library "Data source import will create the following table in our own account: dept, Emp. The following code overrides this as an instance. An overview of two tables

2. Table Union

Select  from where emp.deptno=dept.deptnoSelectfromjoinon  = Emp.deptno

As above, the above two tables are combined with DEPTNO, but one is to select all the tables, then filter, and the other is to make a join

The most common or join

3. Subqueries for SQL

Select   from (Select from EMP         joinon=  Emp.deptno         where = ' DALLAS ' OR = ' New York '    )

Query sets of queries, in the actual work environment is very common. It is not difficult to use () to enclose the query.

You can copy the code into a code editor such as notepad++, see the specific query nesting relationship, and then a layer of anti-push logic.

4. Create a new field in select

Select creates a new field that you can create directly with ... as

 select  ename, loc,  " dallas & NY   " as  city from   EMP  join  dept on  dept.deptno  Emp.deptno  
   
    where  loc 
    =  
     " 
    dallas  
     " 
    or  loc 
    =  
     " 
    new York  
     '  
   

In the same vein, you can create conditional-judged fields with case, just add parentheses. This looks pretty common.

Selectename, Job, ( Case  whenSal> -  Then ' High'                         whenSal< +  Then ' Low'                        Else 'Middle'                    End) asSalary_level fromEmp

Of course, case time can also be used to create a field when grouping statistics, in conjunction with GROUP by.

---Number of people with salary greater than 1500 per jobSelectJob,sum( Case  whenSal> the  Then 1                                                Else 0                    End) assalary_gt1500,Count(*) asPeople_vol fromEMPGroup  byJob

To learn more about the other uses of case, you can also view the following blog post:

Http://blog.sina.com.cn/s/blog_4c538f6c01012mzt.html

Http://www.cnblogs.com/cyrix/articles/1750184.html

5. Data grouping and statistics

General data groupings are grouped by group by, and are used in conjunction with aggregate functions.

The principle is all the columns that follow the SELECT, and if the aggregate function is not used, it must be rewritten in group by.

For example, the following two paragraphs of code, the first paragraph will be error

-- -Error: Use aggregate function count but no group by, or group by a column Select Count (* as from the EMPGroup by Mgr--- Correct: Group by after reference full column selectcount(* as from EMP Group  by Mgr, job

Refer to the following blog post for details: http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

6. Use over (partition by) to group and calculate the corresponding value

The previous group by is primarily used for grouping statistics, and, for example, sum, count, and so on, is a row of data that is combined with group by.

If we are going to output statistics for each row of data, we can use over (partition by) to group and output.

This can be used for: grouping sorting, or grouping aggregation, and so on.

See the code below for an example

---GROUP by job and sort each empSelectjob, ename, Sal,row_number () Over(Partition byJobOrder  bySaldesc) asranking fromEMP---Grouping and finding the average of each group by jobSelectjob, ename, Sal,avg(SAL) Over(Partition byJob asaverage_salary fromEmp

For specific usage, refer to the following blog:

Http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

Http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

7. Data grouping and filtering: where and having

Using Where to filter data is most common in SQL. In its working environment, the primary nested subqueries, or multiple conditions (and or union), are used. Pay attention to the structure.

There is also a data filter to be done after grouping, that is, group by .... It is referenced because the where statement cannot filter the aggregate function

Typical examples are:

SELECT COUNT  from TABLE GROUP  by  having COUNT B > 2

where clause is to group the query results before the wherewhere

having ,having conditions display specific group

--Quote from http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

We can use the following example to try

--
---(because select is the last step of SQL run, it executes later than group by.) So there is no direct use of Empl_vol)Selectcount(* as empl_vol from EMP Group  by Mgr, Job  having Count (*)  > 1

8. Data grouped and sorted and filtered to select the latest batch of data

Group  by XXXX  having SUM > 3  throughORDERby=1;

Q ualify rank optimization. Report execution order, ensuring that enough information is filtered out every step

Http://blog.sina.com.cn/s/blog_4d281a0301016jw2.html

http://community.teradata.com/t5/Database/qualify-rank-over-partition-question/td-p/47965

Http://blog.sina.com.cn/s/blog_62d120530101h7vi.html

About QUALIFY RANK () over combination applications

Looking at search engine discovery,qualify rank () over usage seems to be one of Teradata's unique uses.

Similar to the previously consulted Row_number () over .... just here qualify ... can direct =1 or =2 to get the first sort account

SQL statement Execution Order

Http://www.cnblogs.com/summer_adai/archive/2011/10/28/2227605.html

Add

Show SELECT * FROM TABLEAAA

What fields can be seen in bulk

TERADATA SQL Learning Essay < a >