Database Languages & ANSI Standard SQL

• Topic is interrogating database languages and ANSI Standard SQL

Interrogating Database Languages

• SQL(Structure Query Language) is a language used for various operations on data stored in relational databases, organizing info in 2D tables with rows and columns
• SQL statements manage sets of records instead of one record at a time
• Most managers use SQL, both those in client/server and programming environments, to access data
• SQL features a simple structure, offering flexibility and power and also has very few statements, making mastering it easier
• SQL originated from IBM and was initially named SEQUEL which had simple syntax with few reserved words
• As new products emerged, new reserved words were added, creating "dialects" of SQL similar to SQL for ORACLE, SQL/400 by IBM, Transact SQL Server by Microsoft
• SQL was standardized to establish a language common across database managers
• However, some languages possess "own" commands not included in the American National Standard Institute SQL(ANSI-SQL) reserved words list
• SQL commands are typed in English, case insensitive, but sentence syntax order must always be respected

Subsets of ANSI-SQL

• ANSI-SQL is mainly used to query and modify data stored in relational databases, but the language allows other tasks that classify as follows
• Data Query Language(DQL) is composed of sentences in charge of viewing, organizing, and selecting data from tables, with SELECT as the main statement
• Data Manipulation Language(DML) are statements to add, modify, and erase rows on tables. The statements are INSERT(add), UPDATE(modify), and DELETE(erase)
• Data Definition Language(DDL) are statements to create, modify, rename, or delete objects like CREATE, ALTER, RENAME, and DROP
• Additional functions include granting restrictions to table fields like (CHECK, CONSTRAINT, and NOT NULL), and or establishing table relationships (PRIMARY KEY, FOREIGN KEY, and REFERENCES)
• Data Control Language(DCL) controls data security, granting users permissions to access data with GRANT and REVOKE statements
• Transaction-Processing Language(TPL) are statements that monitor DML commands, assuring coherent operation by using COMMIT, ROLLBACK, and BEGIN TRANSACTION
• Cursor-Control Language(CCL) operates on individual table rows. Affects various records, like FETCH INTO, UPDATE WHERE CURRENT, DECLARE CURSOR

Ways to Use ANSI-SQL

• SQL commands can be executed in different programming languages and environments and the methods of usage are static
• Static SQL;
  • Interactive SQL statements are written directly by the user, and the manager responds directly; managers like Access directly executes commands within the SQL view, instead of using menu options
  • Embedded SQL programming languages like C, Visual Basic, ASP, or JavaScript allow inserting SQL commands between their code lines
  • During program execution, a pre-compiler interprets these SQL orders and sends them to the Database Manager
  • SQLJ embeds SQL statements in Java programs, making programs written in SQL more powerful and faster than those written in the language itself
  • SQL is the language used by most managers and orders execute directly
  • Modular SQL compiles SQL statements separately from the programming language and then links them with the rest of the program's modules
• Dynamic SQL;
  • Dynamic SQL are all the languages that are statically written in SQL, an example being a program allowing a user to type and send an SQL sentence to the manager while running

SQL Statements

• SQL statements are composed of these basic elements;
  • Predefined words have a meaning like SELECT, ORDER BY, etc
  • Names of fields and tables are mere identifiers invented when creating a table and its fields
  • Constants(literals) represent a pre-determined value. Alphanumeric data or text are enclosed in apostrophes
  • Delimiters serve to delimit or separate the other components, including parenthesis, commas, white spaces etc
  • Data types are numerical and alphanumeric
• Numerical Data Types;
  • BIT stores integer values of 0 or 1 that use 1 byte
  • SMALLINT stores a short integer ranging from -32,768 to 32,767 and uses 2 bytes
  • INT stores a long integer ranging between -2,147,483,648 and 2,147,483,647 using 4 bytes
  • DECIMAL numbers have fixed precision and scale with values from -10^38+1 to 10^38-1, and use 4 bytes
  • REAL stores floating-point precision values ranging from -3.40E+38 to 3.40E+38 using 4 bytes
  • FLOAT stores floating-point precision of values ranging between -1.79E+308 and 1.79E+308 using 8 bytes
• Alphanumeric Data Types;
  • CHAR are fields with fixed-length characters per Unicode character, with a max size of 8,000 characters, using 1 byte per character
  • NCHAR are fixed-length Unicode data with a maximum size of 4,000 characters using 1 byte per character
  • VARCHAR are non-Unicode character fields with variable length, with maximum size of 8,000 characters, using 1 byte per character
  • NVARCHAR is Unicode data with variable length, with a maximum size of 4,000 characters using 1 byte per character
  • TEXT stores variable-length character fields not in Unicode with a maximum of 2,147,483,647 characters that use 1 byte per character
• Data Types for Dates and Times
  • DATETIME stores date and time values from January 1, 1753, to December 31, 9999 utilizing 8 bytes
  • TIMESTAMP captures the system instant precisely, occupying 8 bytes
• Binary Data Types
  • BINARY are binary data of fixed length, have a maximum size of 8,000 bytes, and use 1 byte
• Operators
  • Logical operators include AND(the "and" logical operator evaluating 2 conditions and returning a true value only if both are met), OR(the "or" logical operator evaluating 2 conditions and returning a true value if either is met), NOT(the reverse logical operator that returns opposite value of expression)
  • Relational and comparison operators are <, >, <>, <=, >=, =, BETWEEN(defines a range), LIKE(utilized in model comparison), IN(defines a database record)
  • Arithmetical operators are parenthesis(), multiplication(*), division(/), Sum(+), subtraction(-), and module(%)

Clauses

• Clauses are modification conditions employed to define data to be selected or manipulated
  • FROM is utilized to indicate the table to select records
  • WHERE specifies conditions that records must meet to be selected
  • GROUP BY separates selected records into specific groups
  • HAVING is utilized to express the condition that each group must satisfy
  • ORDER BY is utilized to order selected records according to a specific order
• Aggregate Functions are utilized in SELECT clauses in record groups to return a single value to be applied to a set of records
  • AVG calculates the average value of a determined field
  • COUNT returns the number of records in the selection
  • SUM returns the sum of all values in a determined field
  • MAX returns the highest value of a specified field
  • MIN returns the lowest value of a specified field

Selection Queries

• SELECT [predicate] { * | table.* | [ table. ] field1 [ AS alias1 ] [, table.]field2 [ AS alias2 ][, ... ] } FROM table [, ... ] [ WHERE criterion ] [ NOT][IN][(value1, [ value2, [ ... ]])] [ GROUP BY expression_group ] [ HAVING criterion ] [ ORDER BY expression_order [ ASC | DESC ] ] [ clause_subquery [clause_subquery [ ... ] ] ]
  • Predicate is a keyword or clause such as ALL, DISTINCT, and TOP to restrict the number of records to obtain
  • Table is the name of table we're extracting fields from
  • field_n is name of the fields being displayed
  • AS is a keyword utilized to signify an alias that will be used instead of the fieldname
  • Alias an alternative name utlized to display fields
  • Criterion a condition that specifies a record will appear in the query
  • NOT key word utilized as part of other criteria allows us to indicate values to NOT be obtained in a query or together with the IN operator
  • IN keyword used to indicate a list of values inside of where we're querying
  • Value1 is parameter used in the IN clause to determine list of values we wish to querry
  • Expression group specifies which field(s) will be used to create groups
  • Expression order parameter specifies which field(s) will be utilized to order the results and with what criterion
  • ASC | des specifies results of querries that will be organized ascending(ASC) or descending(DESC)
  • Clause subquery a nested query

Basic Queries

• Uses SELECT fields FROM table, the fields are a list of the fields that will populate the table
• To use similar names, type the table name before the field's name like: SELECT Clients.Name, Clients.Phone FROM Clients
• Use "SELECT Type, 'Total:', [Price]+10 FROM trees" to include a column with fixed text
• An Alias can give a new name to this created table, example: "SELECT Type, [Price]+10 AS Total FROM trees;"
• You specify order in the records through ORDER BY list_of_fields WHERE list_of_fields, it represents the field to order, example SELECT Type, Price FROM trees ORDER BY Price
• It is possible to sort records by more than one field like in: SELECT PostalCode, Name, Phone FROM Clients ORDER BY PostalCode, Name
• Use ascending or descending to sort the data

ALL Keywords

• ALL assumes all of the predicates are included, and the Data Engine selects all the logs that meet the conditions in the SQL instruction
• It is not recommended to abuse all because we force the engine to analyze table structure to find our fields, making it faster to individually list them like: SELECT ALL Type FROM trees or SELECT * Type FROM trees
• TOP keyword is a certain number of records from either the beginning or end, otherwise it will default to taking 4 records
• Example SELECT TOP 4 Height FROM trees ORDER BY Height DESC or SELECT DISTINCT TOP 4 Height FROM trees ORDER BY Height DESC
• DISTINCT; omits contained logs duplicated in specific fields, SQL only gives logs with different content, example SELECT DISTINCT Type FROM trees
• WHERE Clause can be used to specify which information will appear after writing SELECT instructions, if this isn't written all logs will appear
• WHERE is optional unless specified and must follow after the FROM command like: SELECT Surnames, Salary FROM Employees WHERE Salary > 21000 or Id, Existance FROM Products WHERE Existance <= Ordered

Value Intervals

• Value Intervals using operator BETWEEN that has the syntax; field [NOT] BETWEEN value1 AND value2 specifying log values using value1 or value2 intervals, with NOT excluding value
• LIKE Operator is utilized to compare an express chain with a model in SQL and model can specify complete value(Ann Marie) or common characters to find range of results(LIKE 'An%')
• The common wildcard characters are %(Any chain composed by one or more characters), _(any character, only one), [range](Any character specified inside range), [^range](Any character that doesn't appear inside rango), #(any digit), and a-z (Value inside 'a' and 'z' includes)
• IN Operators allow to return record in a coded field with a syntax; expression [NOT] IN (value1, value2, ...) Example: SELECT * FROM Orders WHERE Province IN ('Madrid', 'Barcelona', 'Sevilla')

Grouping Records

• GROUP BY combines logs with identical codes into a single register, totalizing every group if SQL functions aggregated are included, and it is optional
• A field list for Group BY can refer to whatever data shows in the FROM chart as long as it isn't in the same SELECT command, SQL aggregations must include elements being grouped
• SELECT Familia_ID, SUM(Stock) FROM Products GROUP BY Families_ID
• HAVING shows any aggregate record by the GROUP BY section that meets the conditions of the HAVING chart, using a WHERE clause to exclude lines that we don't want to group and the HAVING chart to filter over total values
• AVG function is an aggregate used to calculate arithmetic averages, the syntax is an AVG (expression), expression being number data used to calculate with an average, function does not include Null fields in calculation
• COUNT function calculates register numbers obtained during execution, syntax is COUNT(expression), with expression containing a field name, constance or any type of SQL aggregation even from text
• MAX and MIN Functions show minimum or maximum of collection of values inside chart, the syntax being MIN(expression) or MAX(expression), with expressions that can be used with table fields, constance, etc
• SUM function restores groups of data, and the syntax is SUM(expression) with expressions showing whatever value has to be added from other fields
• Example of Totalized on tree data; SELECT Count(Clave) AS CodeAccount FROM trees

Sub Queries

• A subquery is an SELECT instruction nested inside of another's instructions, like SELECT, SELECT ... INTO, INSERT ... INTO, DELETE, or UPDATE
• You can use three forms to create them;
  • Comparison[ANY | ALL | SOME](instruction sql)
  • expression[NOT] IN (instruction sql)
  • [NOT] EXISTS (instruction sql)
• Examples of use are; SELECT * FROM Parents WHERE SNI = ANY (SELECT SNI FROM Childs WHERE Product LIKE 'P3'); or SELECT Id, Existance FROM Products WHERE Existance <= Ordained
• ANY or SOME is used to take registers so they fulfill comparison with any others, the ALL predicates are utilized to recover unique registers only, its more restrictive
• IN Predicate is used to recover unique registers in its main consultation that have a common data, so ˜=ANY is equivalent to IN
• EXISTS predicate is used in fact/false comparisons to determine if sub-queries are returning a value

Using Table Alias(Temporary Name)

• Use in clauses subqueries from chart to refer to elements on FROM charts, to show the wages of any employee being equal to more than any average value; SELECT Last Name, Name, Title, Salary FROM Workers AS T1 WHERE Salary >= (AVG(Salary) FROM Employees WHERE First Title = Employees.Title);
• You can get a list to know name, area and salary from seller's which salaries are bigger than chiefs with; SELECT Last Name, Name, Title, Salary FROM Employees WHERE Title LIKE 'Seller Sale*' AND Salary > ALL (Title Salary FROM Employees WHERE Title LIKE 'Chief*' OR Title LIKE 'Manager*)
• To Obtain a list to know name and prices from all product data use; SELECT DISTINCT Name Product, Unity Price FROM Products WHERE Unity Price = (Price Product FROM Products WHERE NameProduct ='Licorice Anisada');
• To Select the names of personnel that have ordered something; SELECT name, Surname FROM Employees AS E WHERES EXIST (SELECT * FROM Products As O WHERES O.IdEmployee = E.IdEmployee)
• To recover code from the requested data table for code products is: SELECT OrderCode.IdProduct, OrderCode.Quantity, (Show Products.Name FROM Products WHERES Products.IdProduct = ProductOrders) AS TheProducts Form OrderCode WHERES OrderCode.Quantity =150
• If you want to select numbers and spaces for airplanes you use SELECT NumberAirPlane, Spaces FROM Airplanes WHERES Origin ='Madrid' And Exist, SELECT T1.NumberAirlpane FROM Airplanes as T1 WHERE T1.FreeSpace > 0 = NumberAirPlane Airplanes number
• If we now consider a table for all of our providers to find any products you could use SELECT List.ProductRequest FROM ProductRequest GROUP BY ProductRequest.Name

Query Unions

• You can union results between two or more sections, the syntax is; [TABLE] query 1 UNION [ALL] [TABLE] query 2 [UNION [ALL] [TABLE ]query N [...]]
• Union allows you to combine records through various chart settings while All assures every register is returned
• Every command has to have the same data, so use ORDER BY and GROUP BY to get data returned in a specific setting
• For example; SELECT [Companny Name], Town From Providers WHERES Country= 'Brazil' Union SELECT [Company Name], Town FROM Clients WHERES Country= 'Brazil
• All above gives names and cites from providers and clients from Brazil

Linking Charts

• All linkings through charts happens thanks to inner clauses, using code that confirms value is the same, syntax example: SELECT name product name From Categories JOIN Product ON Categories.IdCategory = Product.IdCategory
• For a combination that equals a common combination, it's a binary link only to find common values
• To find both sectors, both code and areas are found, on the opposite to see all the section info use LEFT OUTER JOIN
• If some things aren't working on a memorized data, an OLE errors could appear
• All joins can be linked onto instructions

Table Self-Management

• A table can manage through itself to compare data
• AUTO Join the combination for the table is used in itself, comparing data from two elements, to visualize the total, name and position of every employer use SELECT t.num, t.name, t.position, t.num, w.name, w.position FROM employee AS t, employe AS w WHERES t.NumberBoss = w.Num
• No Commons Combinations is the automatic way to combine and support most of the joins and is commonly combined between NOT, BETWEEN, etc

SQL Collective Functions

• Functions provide total results with function value per total. For example SELECT AVG(gross), SUM((Gross), MAX (Gross) MIIN((Gross) COUNT (Gross) FROM (Employees)
• All functions work with single data value like; DATE gives date and local time from the system or DAY gives days by number
• Here are all other types of functions:
• ABS(x) GIVES ABSOLUTE VALUE | ATAN(x) -> GETS ARC TANGENT | CEIL(x) -> GETS NEXT INN -COS(x) -> COS VALUE |COT(x) -> COTangent | EXP(x) -> EXPONENT VALUE -FLOOR(x) -> BIGGEST INN |INT(x) -> GIVES WIDE NUMBER | LOG(x) -> LOCAL RHYTM -LOG10(x) -> Base Rhythm | MOD(x,y) -> Division Rest | PI -> CONSTANCE -POWER(x,y) -> Value Given to power | RouND(x,y) -> gives the rounding sign of something -SIN (x)- > TRIGONOMETRIC SIN | SQUARE -> QUAD |SQRT VALUE GIVEN TO √ and -Tan(g) -> value to tangente

String Functions

• ASCII(x) Give CODE ASCIIx | CHAR(x) -> ASCII chat | LEFT(x,y) -> y chat at x | LEN(x) -> gives chat numbers,
• LOWER(x) gives lower x | LTRIM(x) -> get rid starts with chat
• Replace(x,y,z)-> enconters the encounters of y with y and replace it for z