Lesson 1

In the previous module, you learned how to define attributes for your table columns. In this module, you will build on these concepts and start to create the actual tables. However, before you create a table, you will need to create a database to store the table.
After completing this module, you will be able to:

1. Create a database
2. Modify a database
3. Delete a database
4. Create a table
5. Modify a table
6. Delete a table

To create a database in SQL Server 2019, the fundamental syntax is as follows:

CREATE DATABASE [database_name]
[CONTAINMENT = { NONE | PARTIAL }]
ON 
[ PRIMARY ]
( NAME = logical_file_name, FILENAME = 'os_file_name' , SIZE = size , 
  MAXSIZE = { max_size | UNLIMITED }, FILEGROWTH = growth_increment )
[ ,...n ]
[ LOG ON 
{ NAME = logical_file_name, FILENAME = 'os_file_name' , SIZE = size , 
  MAXSIZE = { max_size | UNLIMITED }, FILEGROWTH = growth_increment } 
[ ,...n ] ]
[ COLLATE collation_name ]
[ WITH <database_option> [ ,...n ] ]
[;]

In this syntax:

• `CREATE DATABASE [database_name]`: This is the primary statement where you replace `[database_name]` with the name of the database you want to create.
• `CONTAINMENT = { NONE | PARTIAL }`: This optional clause specifies the containment behavior of the database. `NONE` indicates a non-contained database, and `PARTIAL` for a partially contained database.
• `ON`: This clause indicates the beginning of the file declarations for the primary data file.
  • `NAME = logical_file_name`: Specifies the logical name of the file within SQL Server.
  • `FILENAME = 'os_file_name'`: The physical file name on the file system.
  • `SIZE = size`: The initial size of the file.
  • `MAXSIZE = { max_size | UNLIMITED }`: The maximum size to which the file can grow.
  • `FILEGROWTH = growth_increment`: The increment by which the file grows.
• `LOG ON`: This clause is for specifying the transaction log files using similar parameters as for the data files.
• `COLLATE collation_name`: Optional. Specifies the default collation for the database.
• `WITH <database_option>`: Optional. A comma-separated list of database options.
• `[;]`: The semicolon is an optional statement terminator.

It's important to note that SQL Server will apply default settings for many of these parameters if they are not explicitly provided. This syntax provides the flexibility to customize the database creation to your specific needs, including aspects like file size, growth, and location.
The next lesson will introduce you to SQL Server databases.