In the database, you can perform Read, Add, Update, and Delete operations. The basic DML operations, which can be executed with the INSERT, SELECT, UPDATE, and DELETE statements, correspond to Create, Read, Update, and Delete (abbreviated as CRUD) operations.
In the database, you can perform Read, Add, Update, and Delete operations. The basic DML operations, which can be executed with the INSERT, SELECT, UPDATE, and DELETE statements, correspond to Create, Read, Update, and Delete (abbreviated as CRUD) operations.
The command to create a database in a Database Management System (DBMS) is as follows:
Create Database [DBName]
With this command, a database with default settings can be created on the DBMS.
To create a table within a database established on a DBMS, the command provided below can be used. It is essential to determine the database to create a table. You need to use the "use" statement for this.
use [DBName];Create table [TableName] ([fieldName1] type [not null], [fieldName2] type [not null] [default value],[fieldName2] type [not null]… )
After determining the table name with this command, you can define the fields within the table one by one. In field definition, the field name, followed by the data type and whether it can take a null value, should be specified by you. If desired, you can use the default statement to assign a default value when no data is entered.
❓ Did you notice this situation when looking at the example above?
In the query example given above, the default statement is used in the definition of the second field.
A key is defined for one or more fields in a table to characterize a row. A key can be used in different types for different purposes. These are unique key, primary key, and foreign key.
Unique key
Fields defined this way can only be repeated once in the table they are taken. The table does not allow the same value to be added for the second time. However, the value of the unique key can be assigned as Null.
Primary key
The Primary Key acts as an identifier for every row in a table. Your primary key in your data table is essentially a constraint and is unique. Its function is to prevent the values belonging to rows from being mixed up, and it represents that row. Even though it is mostly used as a single field (id, user_id, e-mail, username, SSN, StudentNo, etc.), it can also be formed by combining multiple fields. Numbers are generally preferred as the primary key. You can specify only one primary key in each table. You cannot leave the primary key value blank, and unlike the unique key, you cannot give a "Null" value. It is recommended to choose a primary key in every table in relational databases.
Foreign key
The foreign key, also referred to as a secondary key, allows you to link values that can be entered into a data table with fields in another data table. In summary, it's the primary key of one table being in another table.
Specifying the primary key
In Create table queries, the primary key can be specified in two ways. The first is used when the primary key consists of only one field:
CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));
CREATE TABLE Customer(SID integer PRIMARY KEY,Last_Name varchar(30),First_Name varchar(30));
In the notation below, the primary key can also be used when it consists of more than one field:
CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));
The SELECT query is the query used to query your data. This query can be used to retrieve all data or a specific column from a particular table in your database. You can review the most general form of the Select query below.
Select * from [tableName]
With the query above, you can easily list all the records in the table. The * symbol is used to include all columns in the result set. If you do not want all columns to appear in the result list, you can also write the query as:
Select (fieldName list) from [tableName]
The choice is entirely up to you.
You can use the "as" clause to change column names in the table displayed as a result of the query. For example, if you want a column defined as "firstname" in your table structure to appear as "name" in the return list, you can write your query as follows:
Select firstname as name from employees
In this query, the names of the employees are listed using the employees table. In the list, the column name will appear as "name" instead of "firstname". This statement can also be used by writing a space instead of the "as" clause.
With this clause, you can filter out repeated rows in the result list.
Select distinct (fieldName list) from [tableName]
The ORDER BY query in SQL is used to sort your data according to a specific column.
Select (fieldName list) from [tableName] orderby [field list]
The list resulting from the query can be sorted in ascending or descending order. The default sort is ascending. If you want to sort the list in descending order based on a field, you can use the desc keyword.
Select *from customers order by country desc, city
In the above example, customers are first sorted by their country from Z to A, then customers in the same country are sorted by city from A to Z.
The WHERE query in SQL is used to specify a specific condition. You can filter the result of your query with this condition.
You can use operators such as <, >, <=, >=, =, !=, <>, in, not in, between and, and like to create conditions.
Using the fields in the table and these operators, you can create conditions. Thus, only the expressions that meet the condition you created will be included in the result list.
For example,
Price>40 (price above 40 TL)
City = 'berlin' (in Berlin)
County in ('Japan', 'USA') (in Japan or USA)
unitsInStock between 2 and 5 (stock quantity between 2 and 5)
firstname ‘a%’ (names starting with a)
firstname ‘_a%’ (names with the second letter being a)
To combine conditions;
You can use logical expressions such as And, Or, and Not. For instance,
Price>40 and unitsInStock>20 (price above 40 TL and stock quantity above 20)
companyName like ‘a%’ and country='Germany' (companies in Germany starting with a)
city='İzmir' or city='İstanbul' (located in İzmir or İstanbul)
The INSERT query is used to add new data to your database. This query allows adding a new record to the database. The general structure is;
INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ... ])
In this structure, the names of the fields to be recorded are given in the desired order, then the values are listed after the "values" keyword in accordance with this order. If there's a primary key and fields marked as not null in the field list, they must be present. If you're adding to all fields, the query can be;
INSERT INTO table VALUES (value1, [value2, ... ])
If you want to add multiple records in a single query, it can be written as;
INSERT INTO ''TABLE'' (''column-a'', [''column-b'', ...]) VALUES (''value-1a'', [''value-1b'', ...]), (''value-2a'', [''value-2b'', ...]), ...
Additionally, a list obtained as a result of a "Select" query can also be added to another suitable table. For this, the sub-query approach is used. We will examine the sub-query approach in detail in module 7.
The DELETE query allows you to delete a specific record or all records that meet a specific condition from the database. The query to delete a record is:
DELETE FROM table_name [WHERE condition];
The "UPDATE" query is used to update existing data in your database. This query can update a specific column of a specific record or a specific column of all records. The update query is:
UPDATE table_name SET column_name = value [, column_name = value ...][WHERE condition]
In the database, you can perform Read, Add, Update, and Delete operations. The basic DML operations, which can be executed with the INSERT, SELECT, UPDATE, and DELETE statements, correspond to Create, Read, Update, and Delete (abbreviated as CRUD) operations.
In the database, you can perform Read, Add, Update, and Delete operations. The basic DML operations, which can be executed with the INSERT, SELECT, UPDATE, and DELETE statements, correspond to Create, Read, Update, and Delete (abbreviated as CRUD) operations.
The command to create a database in a Database Management System (DBMS) is as follows:
Create Database [DBName]
With this command, a database with default settings can be created on the DBMS.
To create a table within a database established on a DBMS, the command provided below can be used. It is essential to determine the database to create a table. You need to use the "use" statement for this.
use [DBName];Create table [TableName] ([fieldName1] type [not null], [fieldName2] type [not null] [default value],[fieldName2] type [not null]… )
After determining the table name with this command, you can define the fields within the table one by one. In field definition, the field name, followed by the data type and whether it can take a null value, should be specified by you. If desired, you can use the default statement to assign a default value when no data is entered.
❓ Did you notice this situation when looking at the example above?
In the query example given above, the default statement is used in the definition of the second field.
A key is defined for one or more fields in a table to characterize a row. A key can be used in different types for different purposes. These are unique key, primary key, and foreign key.
Unique key
Fields defined this way can only be repeated once in the table they are taken. The table does not allow the same value to be added for the second time. However, the value of the unique key can be assigned as Null.
Primary key
The Primary Key acts as an identifier for every row in a table. Your primary key in your data table is essentially a constraint and is unique. Its function is to prevent the values belonging to rows from being mixed up, and it represents that row. Even though it is mostly used as a single field (id, user_id, e-mail, username, SSN, StudentNo, etc.), it can also be formed by combining multiple fields. Numbers are generally preferred as the primary key. You can specify only one primary key in each table. You cannot leave the primary key value blank, and unlike the unique key, you cannot give a "Null" value. It is recommended to choose a primary key in every table in relational databases.
Foreign key
The foreign key, also referred to as a secondary key, allows you to link values that can be entered into a data table with fields in another data table. In summary, it's the primary key of one table being in another table.
Specifying the primary key
In Create table queries, the primary key can be specified in two ways. The first is used when the primary key consists of only one field:
CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));
CREATE TABLE Customer(SID integer PRIMARY KEY,Last_Name varchar(30),First_Name varchar(30));
In the notation below, the primary key can also be used when it consists of more than one field:
CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));
The SELECT query is the query used to query your data. This query can be used to retrieve all data or a specific column from a particular table in your database. You can review the most general form of the Select query below.
Select * from [tableName]
With the query above, you can easily list all the records in the table. The * symbol is used to include all columns in the result set. If you do not want all columns to appear in the result list, you can also write the query as:
Select (fieldName list) from [tableName]
The choice is entirely up to you.
You can use the "as" clause to change column names in the table displayed as a result of the query. For example, if you want a column defined as "firstname" in your table structure to appear as "name" in the return list, you can write your query as follows:
Select firstname as name from employees
In this query, the names of the employees are listed using the employees table. In the list, the column name will appear as "name" instead of "firstname". This statement can also be used by writing a space instead of the "as" clause.
With this clause, you can filter out repeated rows in the result list.
Select distinct (fieldName list) from [tableName]
The ORDER BY query in SQL is used to sort your data according to a specific column.
Select (fieldName list) from [tableName] orderby [field list]
The list resulting from the query can be sorted in ascending or descending order. The default sort is ascending. If you want to sort the list in descending order based on a field, you can use the desc keyword.
Select *from customers order by country desc, city
In the above example, customers are first sorted by their country from Z to A, then customers in the same country are sorted by city from A to Z.
The WHERE query in SQL is used to specify a specific condition. You can filter the result of your query with this condition.
You can use operators such as <, >, <=, >=, =, !=, <>, in, not in, between and, and like to create conditions.
Using the fields in the table and these operators, you can create conditions. Thus, only the expressions that meet the condition you created will be included in the result list.
For example,
Price>40 (price above 40 TL)
City = 'berlin' (in Berlin)
County in ('Japan', 'USA') (in Japan or USA)
unitsInStock between 2 and 5 (stock quantity between 2 and 5)
firstname ‘a%’ (names starting with a)
firstname ‘_a%’ (names with the second letter being a)
To combine conditions;
You can use logical expressions such as And, Or, and Not. For instance,
Price>40 and unitsInStock>20 (price above 40 TL and stock quantity above 20)
companyName like ‘a%’ and country='Germany' (companies in Germany starting with a)
city='İzmir' or city='İstanbul' (located in İzmir or İstanbul)
The INSERT query is used to add new data to your database. This query allows adding a new record to the database. The general structure is;
INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ... ])
In this structure, the names of the fields to be recorded are given in the desired order, then the values are listed after the "values" keyword in accordance with this order. If there's a primary key and fields marked as not null in the field list, they must be present. If you're adding to all fields, the query can be;
INSERT INTO table VALUES (value1, [value2, ... ])
If you want to add multiple records in a single query, it can be written as;
INSERT INTO ''TABLE'' (''column-a'', [''column-b'', ...]) VALUES (''value-1a'', [''value-1b'', ...]), (''value-2a'', [''value-2b'', ...]), ...
Additionally, a list obtained as a result of a "Select" query can also be added to another suitable table. For this, the sub-query approach is used. We will examine the sub-query approach in detail in module 7.
The DELETE query allows you to delete a specific record or all records that meet a specific condition from the database. The query to delete a record is:
DELETE FROM table_name [WHERE condition];
The "UPDATE" query is used to update existing data in your database. This query can update a specific column of a specific record or a specific column of all records. The update query is:
UPDATE table_name SET column_name = value [, column_name = value ...][WHERE condition]
