More SQL

March 29, 2018 antony Data Management, Managing Data, Year 11 Semester 2 0

Calculated field

Calculated fields are attributes which have some calculation performed on them.  At the simplest it could be

SELECT productPrice * 0.2

It should be noted calculations can be performed in both SELECT statements and WHERE clauses.

Concatenated field

A concatenated field joins two or more attributes together, which can then be used later in the SQL statement.

For example

SELECT CONCAT(Customers.fName, " ", Customers.lName) AS fullName
FROM Customers
WHERE fullName = 'Joe Brown';

however,  you may also see

SELECT fullName(Customers.fName, " ", Customers.lName) 
FROM Customers 
WHERE fullName = 'Joe Brown';

Note the ” ” is simply a space character between the first and last name.

Aggregation

Aggregation is a function which groups multiple records together and performs some logic or calculation with them.

For example

SELECT MAX(productPrice)

Other examples are MIN(), COUNT(), AVG(), SUM()

Insert

It is for the purpose of inserting new records using an SQL statement.

Update

When we need to change data in a record we use UPDATE in an SQL statement.

Delete

When we need to delete records we use DELETE in an SQL statement.

Make table

Databases obviously are collections of one or more tables, therefore there is a command to make a table, in non-Microsoft speak this is more commonly known as CREATE TABLE.