Please enable JavaScript.
Coggle requires JavaScript to display documents.
Chapter 4: SQL, NURUL SYAFIQAH, NURUL SYAFIQAH, Siti Khatijah, AFIQAH…
Chapter 4: SQL
Constraint
- SQL constraints are used to specify rules for the data in a table.Constraints are used to limit the type of data that can go into a table.
- This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
- Column level constraints apply to a column, and table level constraints apply to the whole table.
Primary key
- A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
Foreign key
- Prevents actions that would destroy links between tables
Unique
- Ensures that all values in a column are different
Default
- Sets a default value for a column if no value is specified
Check
- Ensures that the values in a column satisfies a specific condition
Not Null
- Ensures that a column cannot have a NULL value
SQL QUERIES
With SQL, we can query a database and have a result set returned.
-
DUPLICATE ROWS
The default display of queries is all rows, including duplicate rows.
SELECT address FROM employee ;
SELECT DISTINCT address FROM employee;
-
-
Stephanie Audrey
-
-
It can also be used to add ,modify ,or drop a column from an existing table.
Syntax : ALTER TABLE table_name (action field_definition, action field_definition,);
-
Create Table
Primary Key designations contain both a NOT NULL and a UNIQUE specification to enforce entity integrity.
The list of attributes is enclosed in parentheses. A comma is used after each attribute and its characteristics have been defined. The command sequence end with semicolon.
Using a Subquery
-
-
Example : CREATE TABLE employee2
AS SELECT * FROM employee;
OR
CREATE TABLE employee2
AS SELECT emp_id, emp_name
FROM employee;
-
AGGREGATE FUNCTIONS
COUNT
• return the number of rows containing not null values
for example :
SELECT COUNT (film_id)
FROM film
WHERE special_features LIKE
'%Deleted Scenes%';
SUM
•The sum of all values for a selected attribute in a given column
for example:
SELECT SUM(amount)
FROM payment;
AVG-for specified column
MAX-maximum value encountered in a given column
MIN-minimum value encountered in a given column
MAX,MIN AND AVERAGE
for example:
SELECT
min( amount ) AS MIN,
max( amount ) AS MAX,
avg( amount ) AS AVERAGE
FROM payment;
ROUND FUNCTION
rounds the column, expression or value to n decimal places.
Example:
SELECT ROUND (45.933,2),
ROUND (45.899,0),
ROUND (45.899,-1)
FROM DUAL;
-
ADD COLUMN
To add a column to an existing table, the syntax is:
ALTER TABLE table_name ADD column_name column-definition;
For example:
ALTER TABLE staff
ADD email varchar(50);
This will add a collumn called email to the staff table
Add multiple columns
To add multiple columns to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD ( column_1 column-definition,
column_2 column-definition, ...
column_n column_definition );
For example:
ALTER TABLE staff
ADD ( email varchar(50),
city varchar(45) );
This will add two columns (email and city) to the staff table.
-
-
ADVANTAGES - Used with any DBMS system with any vendor.
-No coding needed: It is very easy to manage the database systems without any need to write the substantial amount of code by using the standard SQL.
Disadvantages of SQL
- Complex interface, SQLhas difficult interface that makes few users uncomfortable while dealing with the Database
- Partial control,Due to hidden business rule, complete control is not given to the database.
-
-
-
-
-
-
-
-
-
-
-