In the previous SQL Basics posts, we’ve looked at querying our data, CRUD operations and transactions.<\/p>\n
Let’s now look at creating databases, tables etc. These statements come under the acronym DDL (Database Definition Language).<\/p>\n
Creating a database<\/strong><\/p>\n We can create a database easily in SQL Server using<\/p>\n Now this is not accepted by every SQL type database, for example file based databases would probably not support this as the existence of the file is the database. <\/p>\n Creating Tables<\/strong><\/p>\n To create a table on a database, we might use the following command in SQL Server<\/p>\n This will ensure that we do not need to prefix our table definitions etc. with the database name. So we can create a table as<\/p>\n Note: the types supported by different databases may differ, but generally you’ll have numeric, string and date\/time types (at least).<\/em><\/p>\n In the example Person table, we created a table with an Id, FirstName, LastName and Age. We’ve not created any primary key for this, so let’s now delete (or DROP) the table and try again with the following (see below for how to DROP the table)<\/p>\n Now we’ve also been more explicit in stating whether columns may be NULL (Note: NULL is the default for a column and some databases may not support explicit setting to NULL as it’s assumed NULL already). We’re using IDENTITY to create an auto incrementing ID. This may not be available in all SQL databases. Then finally we create a primary key constraint on the table named PK_Person.<\/p>\n DROP TABLE<\/strong><\/p>\n To delete a table we call<\/p>\n obviously all the data will be lost when we drop a table. We also may need to remove relationships to the Person table first, if any exist.<\/p>\n ALTER TABLE<\/strong><\/p>\n As it’s name suggests, ALTER TABLE allows us to amend a TABLE. It may also be used as part of the TABLE creation process when foreign keys exist with tables which have not yet been created. i.e. we might create all the tables then ALTER TABLE to create the foreign keys etc.<\/p>\n Let’s just make a simple change to add a Nationality field to the table<\/p>\n Whoops Nationality is a little on small size, so we need to alter this column, let’s use<\/p>\n Or we could remove the column thus<\/p>\n In the previous SQL Basics posts, we’ve looked at querying our data, CRUD operations and transactions. Let’s now look at creating databases, tables etc. These statements come under the acronym DDL (Database Definition Language). Creating a database We can create a database easily in SQL Server using CREATE DATABASE Employee; Now this is not accepted […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22,10],"tags":[],"class_list":["post-1915","post","type-post","status-publish","format-standard","hentry","category-database","category-sql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1915","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=1915"}],"version-history":[{"count":8,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1915\/revisions"}],"predecessor-version":[{"id":1980,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1915\/revisions\/1980"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=1915"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=1915"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=1915"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nCREATE DATABASE Employee;\r\n<\/pre>\n
\r\nUSE Employee;\r\n<\/pre>\n
\r\nCREATE TABLE Person(\r\n Id int,\r\n FirstName nchar (20),\r\n LastName nchar (20),\r\n Age int\r\n)\r\n<\/pre>\n
\r\nCREATE TABLE Person(\r\n Id int IDENTITY(1, 1) not null,\r\n FirstName nchar (20) not null,\r\n LastName nchar (20) not null,\r\n Age int null,\r\n CONSTRAINT PK_Person PRIMARY KEY CLUSTERED\r\n (\r\n Id ASC\r\n )\r\n)\r\n<\/pre>\n
\r\nDROP TABLE Person;\r\n<\/pre>\n
\r\nALTER TABLE Person \r\nADD Nationality nchar(2) null\r\n<\/pre>\n
\r\nALTER TABLE Person \r\nALTER COLUMN Nationality nchar(20)\r\n<\/pre>\n
\r\nALTER TABLE Person \r\nDROP COLUMN Nationality\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"