To_char (to_date (to_date, 'yyyy / mm') || '/ 01', 'YYYY / MM / DD') - 1, 'YYYY / mm')) Y,
Where x.num = y.num ( )
And x.inbound_qty nVL (y.stock_onhand, 0) <> x.stock_onhand) B
WHERE A.NUM = B.NUM
Description:
SQL:
select * from studentinfo where not exists (select * from student where studentinfo.id = student.id) and department name = ' "& strdepartmentname &"' and professional name = ' "& strprofessionname &"' order by gender, students, the college entrance examination total score
Description:
From the database, the unit of telephone bills (telephone billing, constraints, payment, single source)
SQL:
Select a.user, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy') As Telyear, SUM (Decode (to_char (a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
Sum (decode (a.telfeedate, 'mm'), '02', a.factration) ASFRI,
Sum (decode (a.telfeedate, 'mm'), '03', a.factration) AS Mar,
SUM (decode (a.telfeedate, 'mm'), '04', a.factration) AS APR,
Sum (decode (a.telfeedate, 'mm'), '05', a.factration) AS May,
Sum (decode (a.telfeedate, 'mm'), '06', a.factration) AS JUE,
Sum (decode (a.telfeedate, 'mm'), '07', a.factration) AS JUL,
SUM (decode (to_char (a.telfeedate, 'mm'), '08', a.factration) AS AGU,
Sum (decode (a.telfeedate, 'mm'), '09', a.factration) AS SEP,
Sum (decode (a.telfeedate, 'mm'), '10', a.factration) AS OCT,
Sum (decode (a.telfeedate, 'mm'), '11', a.factration) AS NOV,
Sum (decode (to_char (a.telfeedate, 'mm'), '12', a.factration) AS DEC
From (Select A.user, A.TEL, A.Standfee, B.Telfeedate, B.Factration
From TelFeestand A, Telfee B
Where a.tel = b.telfax) a
Group by a.user, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy')
Description: Four Table Interview Questions:
SQL: Select * from a left inner join b on a.a = B.B Right Inner Join C on A.A = C.C inner Join D on a.a = d.d where .....
Description: Get the smallest unused ID number in the table
SQL:
SELECT (SELECT * from Handle B WHERE B.HANDLEID = 1) Then Min (Handleid) 1 Else 1 End) AS HandleId
From handle
WHERE NOT HANDLEID IN (SELECT A.HANDLEID - 1 from Handle A)
SQL database full manual _1
SQL is an abbreviation for Structured Quevy Language (Structured Query Language). SQL is an operation command set that is designed for the database, which is a fully fully equipped database language. When using it, you only need to issue a "do" command, "how to do" is not considering the user. SQL is powerful, easy to learn, easy to use, has become the foundation of database operation, and now almost all databases support SQL. ## 1 Second, SQL Database Data Architecture SQL Database The data architecture is basically a three-level structure, but the terminology is different from traditional relationship model. In SQL, relational mode (mode) is called "Basic Table"; storage mode (internal mode) is called "Stored File); Sub-mode (outside mode) is called" view "(View" ); The tuning is called "row"; the attribute is called "column". Name symmetrical, such as ^ 00100009A ^: ## 1 Third, the composition of the SQL language first allows us to make us a basic understanding of the SQL language, introduce the composition of the SQL language: 1. A SQL database is a table (Table The collection, which is defined by one or more SQL mode. 2. A SQL table consists of a line set, a line is a sequence (collection), each column corresponding to a data item. 3. A table or a basic table or a view. The basic table is a table that is actually stored in the database, and the view is the definition of a table consisting of several basic tables or other views. 4. A basic table can span one or more store files, one or more basic tables can also be stored. Each storage file corresponds to the external storage of the previous physical file. 5. Users can use the SQL statement to query the views and basic tables. In the user's point of view, the view and the basic table are the same, no difference, all the relationships (tables). 6. SQL users can be an application or a terminal user. SQL statements can be used in a program in a host language, and the host language has Fortran, Cobol, Pascal, PL / I, C, and ADA languages. SQL users can also be used as a stand-alone user interface for terminal users in interice. ## 1V, operate SQL for the database includes all the operations of the database, mainly consisting of 4 parts: 1. Data definition: This part is also called "SQL DDL", defines the logical structure of the database, including defining databases , Basic table, view, and index 4. 2. Data manipulation: This part is also known as "SQL DML", including two major class operations of data query and data update, where data updates include insert, delete, and update three operations. 3. Data Control: The control of the user access data has the authorization, integrity rules of the view, the transactional statement, and the like. 4. Use of embedded SQL languages: Specify the rules used in the host language program. Below we will introduce: ## 2 (1) Data Definition SQL Data Definition Function Includes Defining Database, Basic Table, Index, and View. First, let's take a look at the basic data type provided by SQL: (, if ^ 00100009B ^) 1. Create a database for the establishment of the database: Database is a data set including multiple basic tables, its statement format : CREATE DATABASE [Other Parameters] where must be unique in the system, can not be repeated, otherwise it will result in data access error.
[Other parameters] varies depending on the specific database implementation system. Example: To create a project management database (XMManage), its statement should be: CREATE DATABASE XMMANAGE (2) Database Delete: Remove the database and its entire content from the system. Its statement format is: DROP DATABASE Example: Delete Project Management Database (XMManage), its statement should be: DROP DATABASE XMMANAGE 2. The definition of the basic table and the change in the independent table is called a basic table, in SQL language One of the unique correspondence corresponds to a basic table. The definition of the basic table refers to the establishment of a basic relational mode, and the change is to delete and modify the basic tables already existing in the database.
(1) Definition of the basic table: Basic Table is a non-exported relationship, and its definition involves the table name, column name, and data type, etc., its statement format is: Create Table [.]
( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL]] ... [, Unique (column name [, column name] ......)] [, Primary Key] [, Foreign Key] ...) Reference
(Column Name [, Column Name]. .....) [, Check (Condition)] [Other Parameters]) Where, .] Indicates that the newly established table is stored in the database; the new table consists of two parts: one is Tables and a set of column names, which are actually stored data (ie, simultaneously store data to tables directly); column names are user-defined, and spaces cannot be used in column names; The data type is the type of standard data type described above; [not null / null] indicates whether the column is allowed to store null values, the SQL language supports the concept of null value, the so-called null value is "not know" or "meaningless" Value, it is worth noting that data "0" and space are not null value, and the system generally allows for null values by default, so the NOT NULL must be explicitly used when it is not allowed, [, unique] will be used according to its specified The order is arranged. Non-empty; [, Foreign Key] ...) Reference
(Column [, Column Name] ...)] is used to specify foreign keys Referring to the integrity constraint, the Foreign Key specifies the relevant list as the foreign key, which refers to the specified column of another table, which is the column in the outer table introduced by Reference, and the system will default a column name when not specified That is the same as the column name of the reference key, it is to note that the reference must be used when using the foreign key, and the foreign key reference integrity constraint condition specifies: the value of the foreign key is the same as the corresponding primary key, or null value (empty value) Specifically different from the implementation system) [, CHECK] is used to check the data in the deposited table using the specified condition to determine its Legitimacy and improve data security.
Example: To create a student situation (student) create table student file: // Create Basic Table Student (st_class char (8), // Definition column ST_CLASS class, data type is 8-bit set string ST_NO CHAR (10) NOT NULL, / / Definition column ST_NO students, type 10-bit set string, non-empty ST_NAME CHAR (8) not null, // Defines the column ST_NAME name, type 8-bit set string, non-empty ST_SEX Char (2), // Define the column ST_SEX gender, type 2-bit set string ST_AGE smallint, // definition column ST_AGE age, type is short-intensive primary key (ST_NO)) // Defines the ST_NO Learning number as the primary key. Example: To create a course setting table (SUBJECT) CREATE TABLE SUBJECT / / Create Basic Table Subject (SU_NO CHAR (4) Not null, // Defines the column SU_NO Class, type 4-bit set string, non-empty su_subject char ( 20) NOT NULL, / / Definition column Su_SUBJECT course name, type 20-bit set string, non-empty su_credit integer, // definition column Su_credit credits, type is long integer su_period integer, // When defining column SU_PERIOD, the type is Long integer Su_PRENO CHAR (4), // Defines the column SU_PRENO Prescription, type 4-bit set string primary key (su_no)) // Defines the SU_NO class as the primary key. Example: To create a student selection table (score) Create Table Score: // Create Basic Table Score (ST_NO CHAR (10), // Definition Column ST_NO Learning No. 10 String SU_NO CHAR (4), / / Define the column SU_NO class, type 4-bit set string sc_score integer null, // definition column SC_SCORE, type long shape, can be an empty value forign key (ST_NO) REFERENCETENT, // Introduced from Table Student Refer to foreign key ST_NO to ensure that the association of this table and the table student and the synchronous forward key (Suno) REFERENCE SUBJECT) // From the table Subject, the reference foreign key SU_NO is introduced from the table Subject to ensure the association and synchronization of the table Subject (2) Deletion of the basic table: to remove a basic table from the database and its full content, its statement format is: DROP TABLE [.] Table name, for example: the table created above Drop Table Student, SUBJECT, Score (3) Basic Table Modifications: After the Basic Table is established and used for a while, it may be necessary to modify the structure of the basic table according to the actual requirements, that is, add new attributes or delete properties.
Increase the statement format of the attribute: ALTER TABLE [.] Table Name Add ( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL] ... [, unique (column name [, column name] ...... [, Primary Key] [, Foreign Key (column name [, Column name] ...) Reference
(Column [, Column Name] ...)] [, Check (Condition)] [Other Parameters]) For example: in the Basic Table Student Join the list of STBORNs, the data type is DATE, and the statement format that cannot be deleted with an empty value ALTER TABLE Student Add (STBORN DATE NOT NULL) is: ALTER TABLE [.] Name Drop ( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL]] ...) For example: Column ST_AGE in Basic Table Student Delete ALTER TABLE Student Drop (ST_AGE) 3. View definitions and delete in SQL, the view is the basic unit of the external mode data structure. It is a table exported from one or several basic tables that draws a "special table" that makes a number of subsets from existing basic tables. This constructor must be implemented using the SELECT statement in SQL. When a view is defined, it is only defined in the data stored in the system, and the data corresponding to the view is not directly stored until the user uses the view to see the corresponding data.
(1) Definition of the view: Defining a view can be implemented using the CREATE VIEW statement, its statement format is: CREATE VIEW view name AS SELECT statement exported from a basic table: Example: Example: Example from the basic table Student export only female student situation view CREATE vIEW WOMANVIEW AS file: // Create a view WOMANVIEW sELECT st_class, st_no, st_name, st_age file: // selection column st_class, st_no, st_name, st_age display fROM student file: // is introduced from the base table WHERE st_sex student = ' Female '// Introduction Condition is gender to "female", pay attention to character variables Use single quotes to export views from multiple basic tables: for example: export only female students and score from the basic tables Student and Score, more than 60 points View CreateView Woman_score as file: // Defining view Womanscore Select Student.st_class, student.st_no, student.st_name, student.st_age, score.sc_score file: // selective display Related column from student.score file: // Introducing WHERE Student.st_SEX = 'and SCORE.SC_SCORE> = 60 and student.st_no = score.st_no file: // Select Condition: Select: "Female" and score is more than 60. And use ST_NO to link two tables. If this view is performed later, simply use statements Select * from Woman_Score File: // where "*" is a wildcard, deletion of all elements (2) view: Used to delete no longer used views, The statement format is as follows: DROP VIEW View Name: Delete the Woman_score view established above Drop View Woman_Score 4. The index definition with the delete index belongs to the physical storage concept, not the logic concept. Abandon the index concept in SQL, use the primary key concept directly. It is worth mentioning that some relationships of DBMS include indexing mechanisms and primary key mechanisms, here we recommend using primary key mechanisms because it takes a lower in system resource and high efficiency. (1) The definition of the index: index is based on the basic table, its statement format is: create [unique] index index name on [.] Table name (column name [ASC / DESC] [, column Name [ASC / DESC]] ...) Here, the reserved word unique indicates that the index value in the base table does not allow duplicate, and if the default indicates that the index value is allowed to repeat in the table; DESC represents descending order according to the index key If the default or ASC represents an ascending arrangement.
Example: Establish indexes in the basic table student, respectively, as ascending and descending, and index values do not allow the Create Unique Index Stindex ON // Creating Index Stindex Student (ST_NO ASC, ST_AGE DESC) / / to Student ST_NO and ST_AGE Establish Index (2) Deletion: DROP INDEX Index Nurse: Delete Strip INDEX STINDEX ## 2 (2) Data Query SQL is a query function, as long as it is data stock Data, can always find it from the database through the appropriate method. The query statement in SQL has only one: SELECT, which can work with other statements to complete all query functions. The full syntax of the SELECT statement can have 6 clauses. The complete syntax is as follows: SELECT target table list or column express collection from Basic table or (and) view collection [WHERE condition expression] [Group By column name collection] [Order By Column Name] [Collection] ...] The semantics of the entire statement are as follows: From the table listed in the FROM clause, select the tuple that meets the conditional expression given in the WHERE clause, and then prescribed in the GroupBy clause (packet subsis) The value packet of the column, then extract those groups that meet the Conditional expressions in the Having clause, press the column name or column expression value to the select clause. The ORDER clause (sort clause) is reordering the target table of the output and can be attached to the ASC (ascending) or DESC (descending) arrangement.
SQL Database Full Handbook_2 In the conditional expression F in the WHERE clause, the following operators and arithmetic functions can occur: Arithmetic comparison operators: <, <=,>,> =, =, <>. Logical operators: and, or, not. Collection operator: Union (and), INTERSECT, ExcePt. Collection member qualification operators: in, NOT IN predicates: exists (existing quantifiers), all, some, unique. Polymer function: AVG (average), min (minimum), max (max), sum (and), count. F, the operation object can also be another SELECT statement, that is, the SELECT statement can be nested. The above just lists several main operations in the WHERE clause, because the conditions in the WHERE clause can be complicated, so the semantic semantic expression can be more complicated than its mathematical prototype. Below, we have used three basic tables as described as examples, demonstrate the application of SELECT: 1. Unconditional Query: Find out the course of all students Select St_no, Su_NO from Score Example: Find all students SELECT SELECT * From student "*" is a wildcard, indicating the value of all attributes indicated by the relationship indicating from the FROM. 2. Conditional query condition query is a query with a WHERE clause, and the object to be queried must meet the conditions given by WHERE clause. Example: Find a student of any less less than 70, class, class, SELECT UNIQUE Student.st_class, student.st_no, student.st_name, student.st_sex, student.st_age, score.su_no, score.score from Student, score where score.score> = 70 and score.stno = student.st_no This uses unique is not from the query result set to repeat line, if you use Distin, you will remove your row. The priority order of the other logical operator is NOT → AND → OR. Example: Students who find the course number C02, the test score does not match the class, the test score is not asked. Single Su_no = 'c02'and score <60 3. Sort query sorting query refers to ascending the query results as defined by specified properties (ASC ) Or descending (DESC) is arranged by the Order By clause. Example: Find a child, and put the results from the big to small alignment Select Unique Su_no from score where score <60 ORDER BY SU_NO DESC 4. Nested Query Nested query refers to the WHERE clause contains Select Subs This sentence is used in more complicated situations that span multiple basic tables.
Example: Finding the course number C03 and the student number of the course is 80 points, name SELECT ST_NO, ST_NAME FROM Student Where STNO INO INO = 'c03' and score> 80) Here need YES: When the query involves multiple basic tables, the nesting query is subjected to the layout, which has the structural programming characteristics. In nested queries, IN is commonly used. If the user can exactly know that the inner query returns a single value, it can also be used to represent the user's requirements. 5. Calculation query calculation query refers to the results of certain functions that can only be calculated through direct use of specific functions (aggregate functions) provided by the system. Commonly used functions include count count (column name) calculating a number of sum (column name) in a column (column name) to sum (this column value is numerical) AVG (this column value is numerical) AVG Column name) Ask average value (this column value is a numeric model) max (column name) Seeking a maximum of the maximum min (column name) in a column value in a column value: seeking male student Total number and average age Select count (*), avg (ST_AGE) from home: Samples of students in the course: Number of students in the course Select Count (DistINCT ST_NO) from score: This must be added to Distinct, because Some students may elect a multi-do-course, but only one person is counted, so it is necessary to filter with Distinct. ## 2 (3) Data Update Data Updates include data insertion, deletion, and modify operations. They are completed by the INSERT statement, the delete statement, and the UPDATE statement. These operations can be performed on any of the basic tables, but there is a limit on the view. Where the view is exported by a single basic table, the insertion and modification operation can be performed, but the delete operation cannot be performed; the above three operations cannot be performed when the view is exported from a plurality of basic tables. 1. There are two ways to insert the data insertion of the data into SQL: one is the insertion of the cell group, the other is insertion of the multi-group. Insertion of unit group: Insert a grade tuple (100002, C02, 95) to the Basic Table Score, using the following statement: Insert Into Score (ST_NO, SU_NO, SCORE) VALUES ('100002', 'C02', 95) Thereby, the insert statement format of the cell group can be given: INSERT INTO table name (column name 1 [, column 2] ...) VALUES (column value 1 [, column value 2] ...) where the column name is to insert The value of the column name collection, the column value sequence is the corresponding value to be inserted. If inserted a full column value of a table, the column name can omit the unwritten, such as the above (ST_NO, SU_NO, SCORE) can be omitted; if inserted into a table value, the corresponding column name must be listed. At this time, the column names not listed in this relationship take a null value. Insertion of multiple groups: This is a method of inserting the SELECT statement query results into a known basic table.
