Difference Between DDL & DML (DBD)

Data Definition Language (DDL)

It is a collection of SQL commands that can be used to create and change the structure and data type definitions of database objects such as tables, indexes, triggers, views, etc.

Difference Between DDL & DML

The difference between DML and DDL is in the objects they manage. If DDL manages database objects, tables, views, triggers and store procedures. While DML manages records.

DDL commands or statements consist of:

CREATE
ALTER
DROP
GRANT
REVOKE
ANALYZE
AUDIT
COMMENT

Data Manipulation Language (DML)

It is a collection of SQL commands related to the work of processing or manipulating data in a table.

The difference between DML and DDL is in the objects they manage. If DDL manages database objects, tables, views, triggers and store procedures. While DML manages records.

DML commands or statements consist of:

CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE

Reference:

What are the SQL DML commands?

Data manipulation in SQL covers many things about queries. The things that will be discussed here are those related to queries in general, namely:

According to Connoly and Begg (2005, p117), the commands used in data manipulation are:

SELECT : to display the results of a data query in a database
INSERT: to insert data into the database
UPDATE : to update data in the database
DELETE : to delete data in the database.

The purpose of the SELECT statement is to return values and display data from one or more tables in a database. Examples of using the SELECT statement:

SELECT [DISTINCT|ALL] {*|[columnExpression [AS newName] [,...]}
FROM TabelName [alias] [,..]
[WHERE condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]

Explanation of the code above, that;

SELECT specifies the columns you want to display in the output.
FROM specifies the tables to be used.
WHERE filters the displayed data based on specified conditions.
GROUP BY forms groups based on the desired column values.
HAVING filters groups of subjects from certain conditions.
ORDER BY: sets the order for data output.

Example of Beginner Level Database System Design

In this discussion, we will create 1 database and 1 table based on objects around our workplace, by writing the DDL Query, then capturing the MySQL commands and creating a table dictionary as shown in the image below.

Next, we are asked to fill in at least 5 data, then write the query command, and include a screenshot:

Update and Delete the data.
Look for the query command to find out the table type and finally look for what is the difference between using truncate and delete?

Case study

1. Create Database & 1 Table

a. Create a Database & one Table accompanied by a screenshot and in this case we will use DDL Language such as CREATE.

Using the database that was created earlier,

Create a survey tool table,

Displays a table with its description,

It seems like there is something that needs to be corrected, bro, the table names are too close together, let's try changing it to be a more common word, by using DDL language such as ALTER & RENAME.

It seems like there is one more thing we need to change, bro, which is No_urut, it seems too wasteful of words, how about we just change it to No? The meaning is the same, let's do it using DDL language like ALTER & CHANGE.

Before

Process

Results

b. Creating a Table Dictionary. For the Table Dictionary, we will create it with cmd too, okay, let's just follow me,

Al result Dictionary Table

The next stage we will work on question no. 2

2. Fill in at least 5 data and write the query command along with the screenshot.

The DML language that we will use is INSERT & SELECT.

3. Now we will try to do UPDATE & DELETE on the data in the table,

For example, if the NAVCOM GPS has just been damaged due to an incident in the field, let's UPDATE it,

Because the GEB111 battery has dropped so that it is no longer suitable for use, the boss instructed to just throw it away, we will automatically delete it from the tool list, and we will immediately use the DML language DELETE.

Before

Process

Results

4. The next question is that we will find out how to find out the table type in a database, for that let's first visit Mbh.Google,

Finally we found it;

OK bro, let's just apply it straight away,

Hey, wait a minute, bro, it seems like we've slightly violated the procedure, bro, our case study was only instructed to create 1 table, even though we have 2 tables in the database, so let's delete our dictionary table first by using the DDL language DROP,

Introduction

Process

Results

And now it's time for us to find out how to find out our table type,

So, among the table types in MySQL such as MyISAM, InnoDB and HEAP, the table we create is of type MyISAM.

5. The last question we will work on is what is the difference between TRUNCATE and DELETE?

Based on the experience of seniors,

Both can be used to empty the contents of a table, however, DELETE can select each data to be deleted, whereas TRUNCATE cannot, because its characteristic of deleting is "all-or-nothing".
TRUNCATE TABLE <Table Name> ex TRUNCATE TABLE alat_survey; Is a SQL transact used to delete data without backing up the data into a log file. And to run this SQL instruction, someone must act as a superadmin or db_owner, ddl_admin. And TRUNCATE is included in the DDL category. Meanwhile,
DELETE FROM <Table Name> ex DELETE FROM alat_survey; then, the data deleted due to the execution of this query will be backed up into the log file, indirectly it can be said that the deletion process in this way will swell the log file (Database Engine MS SQL Server). And DELETE is included in the DML category.

Demo

https://www.tiktok.com/@santaiarea/video/7022809348246195457?lang=en&is_copy_url=0&is_from_webapp=v1&sender_device=pc&sender_web_id=7022807449564399106

MySQL Basics (fundamentals)

In SQL language, information is generally stored in tables that are logically two-dimensional structures consisting of rows (or records) and columns (or fields). While in a database can consist of several tables.

Some data types in MySQL that are frequently used:

| Tipe data         | Keterangan                                                                                                |
|-------------------|-----------------------------------------------------------------------------------------------------------|
| INT(M) [UNSIGNED] | Angka -2147483648 s/d 2147483647                                                                          |
| FLOAT(M,D)        | Angka pecahan                                                                                             |
| DATE              | Tanggal  Format : YYYY-MM-DD                                                                              |
| DATETIME          | Tanggal dan Waktu  Format : YYYY-MM-DD HH:MM:SS                                                           |
| CHAR(M)           | String dengan panjang tetap sesuai dengan yang ditentukan. Panjangnya 1-255 karakter                      |
| VARCHAR(M)        | String dengan panjang yang berubah-ubah sesuai dengan yang disimpan saat itu. Panjangnya 1 – 255 karakter |
| BLOB              | Teks dengan panjang maksimum 65535 karakter                                                               |
| LONGBLOB          | Teks dengan panjang maksimum  4294967295 karakter                                                         |

CREATE DATABASE AND TABLE

To enter the MySQL program at the prompt run the following command:

C:\> MYSQL (Enter)

Then it will enter MySQL as shown below:

The prompt form mysql> is where you type MySQL commands. Every SQL command must end with a semicolon ; .

The way to create a new database is with the command:

create database databasename;

Example:

create database privatdb;

To open a database, you can use the following command:

use namadatabase;

Example:

use privatdb;

The command to create a new table is:

create table namatabel
(
struktur
);

Example:

Suppose we want to store member data, namely: number, name, email, address, city. While the structure is like the table below:

| Kolom/Field | Tipe data                   | Keterangan                                                               |
|-------------|-----------------------------|--------------------------------------------------------------------------|
| nomor       | int(6) not null primary key | angka dengan panjang maksimal 6, sebagai primary key, tidak boleh kosong |
| nama        | char(40) not null           | teks dengan panjang maksimal 40 karakter, tidak boleh kosong             |
| email       | char(255) not null          | teks dengan panjang maksimal 255 karakter, tidak boleh kosong            |
| alamat      | char(80) not null           | teks dengan panjang maksimal 80 karakter, tidak boleh kosong             |
| kota        | char(20) not null           | teks dengan panjang maksimal 20 karakter, tidak boleh kosong             |

The MySQL command to create a table like the one above is:

create table anggota( nomor int(6) not null primary key, nama char(40) not null, email char(255) not null, alamat char(80) not null, kota char(20) not null
);

Meanwhile, the data that will be filled in the member table is as follows:

| Nomor | Nama            | E-Mail                 | Alamat                    | Kota      |
|-------|-----------------|------------------------|---------------------------|-----------|
| 1     | Arini Nurillahi | arini@hotmail.com      | Jl. Lebak Rejo 7          | Surabaya  |
| 2     | Renny Herlina   | rennyherlina@yahoo.com | Jl. Hayam Wuruk 81        | Bau Bau   |
| 3     | Anon Kuncoro    | anonkuncoro@yahoo.com  | Jl. Candi Permata II/182  | Semarang  |
| 4     | Bayu            | bayu@astaga.com        | Jl. Pemuda 19             | Surabaya  |
| 5     | Riza            | riza@themail.com       | Jl. Karang Menjangan 5    | Surabaya  |
| 6     | Paul            | paul@rocketmail.com    | Jl. Metojoyo A-10         | Malang    |
| 7     | Anita           | anita@netscape.net     | Jl. Teuku Umar 45         | Malang    |
| 8     | Yusuf           | yusuf@hotmail.com      | Jl. Rajawali 78           | Mojokerto |
| 9     | Ali             | ali77@astaga.com       | Jl. Hasanuddin 3          | Mojokerto |
| 10    | Aji             | ajisaka77@yahoo.com    | Jl. Kalilom Lor Kelinci 9 | Surabaya  |
| 11    | Latief          | latif@mail.com         | Jl. Merak 171             | Surabaya  |
| 12    | Supri           | supri@themail.com      | Jl. Sudirman 12           | Malang    |

To insert a row (record) into a MySQL table, do the following:

insert into namatabel values(kolom1, kolom2, kolom3,...);

Example:

insert into anggota values('1','Arini Nurillahi','arini@hotmail.com','Jl.Lebak Rejo
7','Surabaya');

DISPLAYING TABLE CONTENTS

Table contents can be displayed using the SELECT command, the way to write the SELECT command is:

select kolom from namatable;

Example:

To display the number and name columns (fields) in the member table. select nomor, nama from anggota;
To display all columns (fields) in the member table select * from anggota;
To display all columns in the member table located in the city 'Surabaya' select * from anggota where kota='Surabaya';
To display all columns in the member table in name order select * from anggota order by nama;
To count the number of records in the member table select count(*) from anggota;
To display cities in the member table select kota from anggota;
To display cities without displaying the same city in the member table select distinct kota from anggota;
To display the name and email of those who have email at 'yahoo.com' select nama,email from anggota where email like '%yahoo.com';

DELETE RECORD

To delete a record with certain criteria, use the following command:

delete from namatabel where kriteria;

Example: Delete a record from the member table with the number '3' delete from member where number='3';

MODIFYING RECORD

To modify (change) the contents of a particular record, use the following command:

update namatabel set kolom1=nilaibaru1, kolom2=nilaibaru2 ... where kriteria;

Example: Changing the e-mail of member number 12 to ' supri@yahoo.com ' in the members table.

update anggota set email='supri@yahoo.com' where nomor='12';

CONNECTING PHP WITH MySQL

So that the PHP script we create can connect to the MySQL database, we can use the following function:

Main file.php:

<?php 
 
function open_connection() 
{ 
$host=”localhost”; 
$username=”root”; 
$password=””; 
$databasename=”privatdb”; 
$link=mysql_connect($host,$username,$password) or die ("Database tidak dapat dihubungkan!"); 
mysql_select_db($databasename,$link); return $link; 
} 
?>

The contents of the $host, $username, $password and $databasename variables can be adjusted according to the settings on the existing MySQL server.

Example: Displaying member data that has been created using a PHP script.

Example file13.php:

<?php 
// ----- ambil isi dari file utama.php require("utama.php"); 
 
// ----- hubungkan ke database 
$link=open_connection(); 
 
// ----- menentukan nama tabel 
$tablename="anggota"; 
 
// ----- perintah SQL dimasukkan ke dalam variable string 
$sqlstr="select * from $tablename"; 
 
// ------ jalankan perintah SQL 
$result = mysql_query ($sqlstr) or die ("Kesalahan pada perintah SQL!"); 
 
// ------ putus hubungan dengan database mysql_close($link); 
 
// ------ buat tampilan tabel 
echo("<table width=100% cellspacing=1 cellpadding=2 bgcolor=#000000>"); echo("<tr><td bgcolor=#CCCCCC>No</td><td bgcolor=#CCCCCC>Nama</td><td bgcolor=#CCCCCC>E-Mail</td><td bgcolor=#CCCCCC>Alamat</td><td bgcolor=#CCCCCC>Kota</td></tr>"); 
 
// ------ ambil isi masing-masing record while ($row = mysql_fetch_object ($result)) 
{ 
// ----- mengambil isi setiap kolom 
$nomor=$row->nomor; 
$nama=$row->nama; 
$email=$row->email; 
$alamat=$row->alamat; 
$kota=$row->kota; 
 
// ------ menampilkan di layar browser 
echo("<tr><td bgcolor=#FFFFFF>$nomor</td><td bgcolor=#FFFFFF>$nama</td><td bgcolor=#FFFFFF>$email</td><td bgcolor=#FFFFFF>$alamat</td><td bgcolor=#FFFFFF>$kota</td></tr>"); 
} 
echo("</table>"); 
?>

How to Display Database Record Data

View is a virtual table/temporary table whose contents are created with SQL commands. View is an alternative way to view data from one or many tables in a database.

How to Display Data in Database

1. Creating View

The format of the view command is:

create view namaview (daftarfield) as ekspresiselect

Meanwhile, to see the results of the view that has been created, use the following command:

select * from namaview

When creating a view there are several rules as follows:

Vew can only be created on an active database.
The view name must be unique for each user. To differentiate it, the view name is usually given the additional letter v.... or view..... Example: vnasabah or viewnasabah.
Views can be created based on other views.
Queries defined in views cannot contain ORDER BY.
The number of columns/fields listed in the fieldlist must be the same as the number of columns/fields in the select expression.

The field list can be optional. However, certain conditions require the field list to be written because:

There are columns in the view that are derived from arithmetic expressions, functions or constants.
Two or more columns in a view have the same name (usually because they come from joined tables).
I deliberately wanted to name the columns manually.

Here is an example of creating a view named viewtamsis, which will display customer data with the address Jl. Tamansiswa.

create view viewtamsis as select * from nasabah where alamat
like 'Jl. Tamansiswa%'

To see the results, type the following command:

select * from viewtamsis

Below is an example of creating a view named viewwilayah that displays branch name and address data. Then see the results with the SELECT command.

create view viewcabang as select nama_cabang, alamat_cabang
from cabang_bank

Views can also be created from multiple tables, here is an example:

create view viewtransaksi as
select b.norek, a.nama_nasabah, a.alamat_nasabah
from nasabah a, nasabah_rek b, rekening c
where b.norek = c.norek and a.idnasabah = b.idnasabah

The above command can be written as below and the result is the same. Understand it well.

create view viewnasabahrek as
select b.norek, a.nama_nasabah, a.alamat_nasabah
from nasabah a join nasabah_rek b
on a.idnasabah = b.idnasabah
join rekening c on c.norek = b.norek

In addition to the above commands, you can also create views that come from:

Join between tables -- view.
Join between views.

So far, we have created views that are contained in the banking database, namely:

Viewtamsis
Viewbranch
Viewtransaction
Viewnasabahrek

To see a list of views that have been created, type the following command:

select * from sysobjects where type = 'view' and name like 'view%'

As an exercise, please try to be creative in creating views and displaying information according to your wishes.

2. Update View

The command to change the view is almost the same as changing the table, namely using the ALTER command. You can try the following example:

alter view viewtransaksi
select nama_nasabah, alamat_nasabah, c.saldo
from nasabah a, nasabah_rek b, rekening c
where b.nore = c.norek and a.idnasabah = b.idnasabah

To see the results, type the following command:

select * from viewtransaksi

3. Delete View

Basic command to delete a view:

drop namaview1, namaview2, ...

Example:

drop viewtransaksi

Hope this is useful & happy learning!

Collection of Data Manipulation SQL Commands

To display data with many conditions, you can use logical operators, namely: NOT, AND, OR, BETWEEN, LIKE, IN, SOME, ANY and ALL. For NOT, AND and OR operators have logical conditions with standard rules. Learn more about SQL language as DML in this post.

SQL Data Manipulation Collection

To make it easier for you to learn and practice (Learn by Doing), I assume you have imported the northwind database. But if not, please download it HERE first .

1. AND

The following is the SQL command to display records in the products table that have CategoryID=2 and SupplierID=2.

SELECT * FROM Products WHERE CategoryID=2 AND SupplierID=2;

Results:

2. OR

The following is a SQL command to display records in the products table that have CategoryID=2 or SupplierID=2.

SELECT * FROM Products WHERE CategoryID=2 OR SupplierID=2;

Results:

3. BETWEEN

Here is the SQL command to display records in the products table that have UnitsInStock between 20 and 50. Outside of that limit, they are not displayed.

SELECT * FROM Products WHERE UnitsInStock BETWEEN 20 AND 50;

Results:

Knowing the Amount of Turnover

SELECT
p.ProductName, SUM(od.Quantity) Terjual
FROM
products AS p JOIN order_details AS od
ON p.productID=od.productID
JOIN orders AS o
ON od.orderID=o.orderID
WHERE YEAR(o.OrderDate) BETWEEN 1996 AND 1997
GROUP BY 1

Results:

4. IN

The following is a SQL command to display records in a table, by using one of the records as a reference.

For example, I choose ProductID as the reference for SupplierID and CategoryID to display related data. Actually, this example is not quite right, but it can still explain how the IN command works.

SELECT * FROM products WHERE ProductID IN (SupplierID,CategoryID);

Results:

5. ORDER BY (Sort Data)

Sort records using the ORDER BY [ASC | DESC] command. Sorting applies to string and integer/numeric data types. If the sorting type is not mentioned in the sorting, the default is set to ascending. If there is a WHERE clause, the order by is placed after WHERE.

1. Sort in Ascending Order

SELECT * FROM products ORDER BY ProductName;

By default, SQL ORDER is ASCENDING. Although actually it can also be written like this:

SELECT * FROM products ORDER BY ProductName ASC;

Results:

2. Sort by Descending

SELECT * FROM products ORDER BY ProductName DESC;

Results:

3. Sort by Reference Priority

This example uses SupplierID as the reference priority, so its placement must be the leftmost among the other references. While CategoryID will be used as a minority reference.

SELECT * FROM products ORDER BY SupplierID, CategoryID;

Results:

4. Sorting by Descending and Ascending

SELECT * FROM products ORDER BY SupplierID DESC, CategoryID;

Results:

5. Sorting by Ascending and Descending

SELECT * FROM products ORDER BY SupplierID, CategoryID DESC;

Results:

6. Display first and last name

SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC;

Results:

6. RECORD Grouping

To display record grouping, use GROUP BY. The GROUP BY capability will look different from ORDER BY when we use it for Aggregation purposes specifically on columns that have not been grouped.

Example:

Below we will show you how to find out the number of orders that need to be sent to each country.

SELECT COUNT(OrderID), ShipCountry FROM orders GROUP BY (ShipCountry);

Results:

Knowing the Buyer's Name

SELECT
c.ContactName,
s.CompanyName,
COUNT(O.ShipVia)Frequensi
FROM
orders AS O JOIN shippers AS s
ON s.ShipperID=O.ShipVia
JOIN customers AS c
ON c.CustomerID=O.CustomerID
group by 1,2

Results:

7. LIKE

A more detailed discussion of the LIKE Query can be found in SQL String Search . We will briefly provide an example of how to display employees with first names starting with the letter A:

SELECT TitleOfCourtesy,FirstName,LastName
FROM employees WHERE FirstName LIKE 'A%';

Results:

8. UPDATES

First look at the data you want to update (Before UPDATE).

SELECT ProductName,SupplierID,UnitsInStock
FROM products WHERE
SupplierID=1;

Results:

Update Process

UPDATE products SET UnitsInStock=UnitsInStock+5
WHERE SupplierID=1;

Take another look at the data,

The update command is used to change data/records from a table.

Here is the format:

Update namatabel set namakolom = "databaru" where [kondisi]

The following example explains how to update data from the branch table in the address column, where all old addresses will be replaced with 'Jl. Gejayan'.

Upadate cabang set alamat = 'Jl. Gejayan'

If more than one record is changed, use a comma ( , ) and the following format:

Update namatabel set namakolom1="nilai1", namakolom2="nilai2", ......Where [kondisi]

Example:

Update biodata set jur="TI", nilai="B", angkatan="2007" where jk="L"

The command above means changing all records of male students where major=IT, grade=B and class=2007.

9. COUNT

Calculate the number of available data with the condition SupplierID=1;

SELECT count(*) FROM products WHERE SupplierID=1;

Results:

Give the report title as "Total"

SELECT count(*) AS Jumlah
FROM products WHERE
SupplierID=1;

Results:

10. LIMIT

Gives a limit by only displaying a maximum of 10 records randomly

SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY RAND() LIMIT 10;

Results:

Provides a limitation by only displaying a maximum of 5 records

SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC LIMIT 5;

Results:

Providing constraints with parameters;

LIMIT mulai_dari, jumlah_record

Below I will give an example of displaying employee records starting with interval 3 from the 1st sequence, it is the same as displaying records starting from the 4th sequence. Then the maximum number of records I want to display is 3.

Pay attention to the employees report above, interval 0 (1st order) is occupied by Andrew, meaning interval 3 (4th order) is occupied by Laura.

SELECT TitleOfCourtesy,FirstName,LastName
FROM employees ORDER BY FirstName ASC LIMIT 3,4;

Results:

11. AS (ALIAS)

The alias clause is used to rename table columns resulting from the select command.

The basic format:

Select field1 as 'nama1', field2 as 'nama2', ... from tabelasal

Example:

Select id_nasabah as 'ID', nama_nasabah as 'Nama Lengkap',  jk as 'Kelamin' from nasabah

12. SELECT

Used to display records in a table according to certain criteria.

The format of the select command is as follows:

Select (field1,field2, ....) from tabelasal where [kondisi]
group by [ekspresi] Having [kondisi]
order by [ekspresi] [asc | desc]

To display all records in one table the command is:

Select * from cabang

To display multiple fields in one table, the command is:

Select nama, jk, alamat from biodata

Display records in a table under certain conditions:

Select nama, alamat, tgl_lahir from biodata where jk='L' and alamat='Jl. Gejayan'

To display all records in the account table whose balance is more than 350000, the command is:

Select * from rekening where saldo > 350000

13. DELETE

The delete command is used to delete one or more records according to conditions.

Here is the format:

Delete from namatabel where [kondisi]

You need to be careful using the delete command, as it will not confirm the deletion process.

Here is an example:

Delete from cabang where namacabang='Jl. Gejayan'

If you want to delete all records in a table, you don't need to include a condition in the where clause.

14. INSERT

The INSERT command is used to insert records into a table/view. Record insertion depends on when creating the table and its constraints. If a field is declared NOT NULL then the field must be filled.

Example:

Insert into barang values ('KT01','Kartu Mainan','Buah',15000)

Or if all records are not filled, then the fields must be mentioned.

Example:

Insert into barang (kdbrg,nmbrg,hrgsat) values ('KT01','Kartu Mainan',15000)

15. UNIQUE

Unique, the same function as Primary Key. The difference lies in the Primary Key which can only be created once in a table and Unique can be created multiple times in a table.

If you want some fields to be unique then declare them as Unique which is not included in the Primary Key field.

16. FOREIGN KEY ... REFERENCES

Foreign Key ... References, is a guest key or relationship.

Derived from the primary key in the master table that is placed in the transaction table. Foreign Key constraints can only reference fields that have a Primary Key or Unique constraint in the referenced table.

17. PRIMARY KEY

Primary Key, is a primary key that is unique and can only be created per table.

18. CONSTRAINT

Constraints are features that enforce data integrity, maintain data quality and create special indexes for tables and their fields.

Constraint is also an optional keyword that marks the beginning of the definition of Primary Key, Foreign Key, Unique, Check and Default constraints.

For example, in the field ag only values ('I','K','P','B','H') are allowed to be input. The constraint is applied when creating the table.

Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))

19. DEFAULT VALUES

Default value, meaning the default value of the attribute is 1.

Default, is the default value that can be applied to all fields except fields of type TimeStamp or Identity.

The example below shows that student status 1 = unmarried, and a value of 0 = married.

Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))

20. IDENTITY

Identity, indicates a field that will automatically be filled with values in increments of 1 starting from the number 1. Identity is the equivalent of auto_increment in MySQL.

Identity, used to provide unique values with certain value increments. Used together with Primary Key and is of numeric type.

Only one field is identity in a table. You must specify a start value and an end value or neither. If not specified then the default value is (1,1).

Example:

Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))

21. NOT NULL

Not Null, states that a field's value is unknown. Not Null should not be used on fields that have identity or primary key properties.

Example:

Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))

Difference Between DDL & DML (DBD)

Data Definition Language (DDL)

Data Manipulation Language (DML)

Reference:

What are the SQL DML commands?

Example of Beginner Level Database System Design

Case study

1. Create Database & 1 Table

2. Fill in at least 5 data and write the query command along with the screenshot.

3. Now we will try to do UPDATE & DELETE on the data in the table,

4. The next question is that we will find out how to find out the table type in a database, for that let's first visit Mbh.Google,

5. The last question we will work on is what is the difference between TRUNCATE and DELETE?

Demo

MySQL Basics (fundamentals)

CREATE DATABASE AND TABLE

DISPLAYING TABLE CONTENTS

DELETE RECORD

MODIFYING RECORD

CONNECTING PHP WITH MySQL

How to Display Database Record Data

1. Creating View

2. Update View

3. Delete View

Collection of Data Manipulation SQL Commands

1. AND

2. OR

3. BETWEEN

Knowing the Amount of Turnover

4. IN

5. ORDER BY (Sort Data)

1. Sort in Ascending Order

2. Sort by Descending

3. Sort by Reference Priority

4. Sorting by Descending and Ascending

5. Sorting by Ascending and Descending

6. Display first and last name

6. RECORD Grouping

Example:

Knowing the Buyer's Name

7. LIKE

8. UPDATES

9. COUNT

10. LIMIT

11. AS (ALIAS)

12. SELECT

13. DELETE

14. INSERT

15. UNIQUE

16. FOREIGN KEY ... REFERENCES

17. PRIMARY KEY

18. CONSTRAINT

19. DEFAULT VALUES

20. IDENTITY

21. NOT NULL

Post a Comment

نموذج الاتصال