As mentioned before, relations / tables / sets are an unordered collection of rows, or tuples.
Let's retrieve all the content in table products:
SELECT*FROMproducts;
SELECT *
This means brings all the columns; it could also be written as SELECT id, name
FROM products
List of tables to operate on. In this case, we're talking about table products
Here's a possible result:
id
name
1
Sport shoes A
3
Suit C
2
Sport watch B
Why possible? Because there is no specific ordering of rows; it all depends on how the DB system stores the rows, the order in which rows were INSERTed, and others
Ordering
In order to establish a specific order, we must use the ORDER clause:
SELECTid,nameFROMproductsORDERBYid;
id
name
1
Sport shoes A
2
Sport watch B
3
Suit C
In reverse order, by name:
SELECTid,nameFROMproductsORDERBYnameDESC;
id
name
3
Suit C
2
Sport watch B
1
Sport shoes A
The ORDER clause may list more than one column, or expression; in this simple case, it doesn't make much sense
Filtering
Let's see how to limit the number of rows, based on filtering conditions:
Expressions like id * 2 and functions like upper(name)
the AS key word is used to give names to the expressions; if it were not used, the calculated columns would have default names, which vary depending on the DB system used
sales s and products p; this is not necessary, but instead of typing sales.id_product, product.name and sales.amount, aliases help us to shorten the sentences
If the column names are unique among the invoked tables, there is no strict need to preface the column with the table or alias names, although it's a good practice. In fact, if in the future one of the column names are repeated, what worked so far will stop to do so, as the DB engine wouldn't know what table the column referrer to
the GROUP BY clause needs to include p.name; otherwise, an error should be triggered (again, not all DB engines do so)
Types of JOIN:
INNER JOIN: Rows from both tables must be present (the default)
LEFT JOIN: Rows from the left-side table must exist, for columns in the right-side table missing, NULL values are used
RIGHT JOIN: If rows from the left-side table do not exist, NULL values are used instead
CROSS JOIN: Cartesian product from both tables are retrieved, as long as the 'ON' clause is fulfilled
As an example of a CROSS JOIN, which is not much used, look at this simple SELECT:
Simply naming the tables without any filtering condition, is equivalent to a CROSS JOIN:
id_product
name_product
id_seller
name_seller
1
Sport shoes A
234
John S.
1
Sport shoes A
281
Luisa G.
1
Sport shoes A
341
Mary T.
2
Sport watch B
234
John S.
2
Sport watch B
281
Luisa G.
2
Sport watch B
341
Mary T.
3
Suit C
234
John S.
3
Suit C
281
Luisa G.
3
Suit C
341
Mary T.
This SELECT is just an example of a CROSS JOIN result.
Updating table contents with UPDATE
The UPDATE sentence is used for updating rows
An example follows:
UPDATEsellersSETname='María T.'WHEREid=341;
After this change, look at the table contents:
SELECT*FROMsellersORDERBYid;
id
name
234
John S.
281
Luisa G.
341
María T.
Important points:
Limit the UPDATE with a WHERE; otherwise, the update will impact all rows in a table, all of them, in one transaction
Not all updates are guaranteed to success; for instance, in the schema with all constraints in place, this UPDATE will fail:
UPDATEsellersSETid=235WHEREid=234;
If you try this, you'll see that it fails, as there are relational integrity constraints which prevent tables sellers and products to update their PK if there is at least one row in the sales table that point to those values, as is the case in the example here
See the error with PostgreSQL:
ERROR: update or delete on table "sellers" violates foreign
key constraint "sales_seller_fkey" on table "sales"
DETAIL: Key (id)=(234) is still referenced from table "sales".
See it with MariaDB:
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
(`course`.`sales`,
CONSTRAINT `sales_ibfk_2`
FOREIGN KEY (`id_seller`) REFERENCES `sellers` (`id`)
)
Deleting table contents with DELETE
The DELETE DML sentence is used to remove rows from tables
Here's an example:
DELETEFROMsalesWHEREquantityBETWEEN(4AND5);
After deleting these rows, the contents of the table are:
As is the case with the failed UPDATE and constraints are in place, certain DELETE queries will fail
For instance:
DELETEFROMsalesWHEREid=234;
If the FOREIGN KEY constraints established in part 2 for table sales had the ON UPDATE CASCADE ON DELETE CASCCADE, neither the UPDATE nor the DELETE would fail:
For the UPDATE, it would modify the values of the foreign keys in table sales
For the DELETE, it would also remove rows from the sales table
Top comments (0)
Subscribe
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)