{"id":47,"date":"2016-01-10T20:43:17","date_gmt":"2016-01-10T20:43:17","guid":{"rendered":"http:\/\/mr-west.uk\/sql\/?page_id=47"},"modified":"2016-07-24T09:22:42","modified_gmt":"2016-07-24T09:22:42","slug":"creating-a-database-1","status":"publish","type":"page","link":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/","title":{"rendered":"Creating a database part 1"},"content":{"rendered":"\n<div class=\"arconix-tabs-horizontal\"><ul class=\"arconix-tabs\"><li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Starter\"><a class=\"\" href=\"#tab-Starter\">Starter<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Creatingadatabase\"><a class=\"\" href=\"#tab-Creatingadatabase\">Creating a database<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Datatypes\"><a class=\"\" href=\"#tab-Datatypes\">Data types<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Creatingtables\"><a class=\"\" href=\"#tab-Creatingtables\">Creating tables<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Plenary\"><a class=\"\" href=\"#tab-Plenary\">Plenary<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Resources\"><a class=\"\" href=\"#tab-Resources\">Resources<\/a><\/li><\/ul>\n<div class=\"arconix-panes\"><div class=\"arconix-pane pane-Starter\">\n<p>Have a go at this quiz revise the previous lesson&#8217;s learning.<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Creatingadatabase\">\n<h3>sqlite3 <span style=\"color: #0000ff;\">database_name.db<\/span><\/h3>\n<p>Creating a database in SQLite is easy. In fact, some of you may already have created a new database by accident!<\/p>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-bullseye'><\/i><div class=\"arconix-box-content\">\n<p><strong>Challenge 13<\/strong><\/p>\n<p>You are a massive fan of formula one racing and have decided to keep track of the race times of each competitor.<\/p>\n<p>Create a new database, titled<strong> racing.db<\/strong><\/p>\n<\/div><\/div>\n<\/div>\n<div class=\"arconix-pane pane-Datatypes\">\n<div class=\"arconix-accordions arconix-accordions-vertical arconix-accordions-1\"><div class=\"arconix-accordion-title accordion-DataTypes\"><i class=\"fa\"><\/i><p>Data Types<\/p><\/div><div class=\"arconix-accordion-content\">\n<h2>SQLite Data types<\/h2>\n<p>Before we can start creating tables, we need to first think about the data we are going to store in the table and we need to answer the following questions:<\/p>\n<ul>\n<li>What kind of data are we going to store in each column?<\/li>\n<li>Are we going to have a default value for each column?<\/li>\n<li>Are we going to allow empty cells<\/li>\n<\/ul>\n<p>Different databases support different data types, but SQLite supports the following:<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-INTEGER\"><i class=\"fa\"><\/i><p>INTEGER<\/p><\/div><div class=\"arconix-accordion-content\">\n<h3><span style=\"color: #0000ff;\">column_name<\/span> INTEGER<\/h3>\n<p>Used for: Whole Numbers<\/p>\n<p>Example: 445<\/p>\n<p>Python equivalent: Integer<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-REAL\"><i class=\"fa\"><\/i><p>REAL<\/p><\/div><div class=\"arconix-accordion-content\">\n<h3><span style=\"color: #0000ff;\">column_name<\/span>\u00a0REAL<\/h3>\n<p>Used for: Numbers with decimal places<\/p>\n<p>Example: 3.142<\/p>\n<p>Python equivalent: Float<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-TEXT\"><i class=\"fa\"><\/i><p>TEXT<\/p><\/div><div class=\"arconix-accordion-content\">\n<h3><span style=\"color: #0000ff;\">column_name<\/span>\u00a0TEXT<\/h3>\n<p>Used for: Characters words or sentences<\/p>\n<p>Example: &#8220;hello&#8221;<\/p>\n<p>Python equivalent: String<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-BLOB\"><i class=\"fa\"><\/i><p>BLOB.<\/p><\/div><div class=\"arconix-accordion-content\">\n<h3><span style=\"color: #0000ff;\">column_name<\/span>\u00a0BLOB<\/h3>\n<p>Used for storing data exactly as entered.<\/p>\n<p>Example: used to stored URLs, file names and objects.<\/p>\n<p>Python equivalent: Objects ( We&#8217;ve not covered them)<\/p>\n<\/div><\/div>\n<\/div>\n<div class=\"arconix-pane pane-Creatingtables\">\n<div class=\"arconix-accordions arconix-accordions-vertical arconix-accordions-1\"><div class=\"arconix-accordion-title accordion-Databasedesignthewrongway-FlatFileDatabases\"><i class=\"fa\"><\/i><p>Database design the wrong way - Flat File Databases.<\/p><\/div><div class=\"arconix-accordion-content\">\n<h3>CREATE TABLE <span style=\"color: #0000ff;\">table_name<\/span>( <span style=\"color: #0000ff;\">col1_name<\/span> <span style=\"color: #0000ff;\">COL1_DATATYPE<\/span>, <span style=\"color: #0000ff;\">col1_name<\/span> <span style=\"color: #0000ff;\">COL2_DATATYPE<\/span>);<\/h3>\n<p>Before we can start creating tables, we need to think about what data we are going to store in each table and how this data is going to relate to other tables in the database.<\/p>\n<p>First of all we are going to create a simple table using<strong> flat file table design.<\/strong><\/p>\n<p>The best way to think of flat file table design is a spreadsheet file. Data is stored in rows and columns, often on a single sheet. If you want to store different types of data, you can store the different types in a number of different sheets, but the data stored in each sheet is not linked in any way.<\/p>\n<p>This type of table is very easy to create, but it causes a number of problems, including:<\/p>\n<ul>\n<li>Data duplication &#8211; you end up repeatedly storing the same data in different places<\/li>\n<li>Very difficult to create queries that combine data from different tables.<\/li>\n<\/ul>\n<\/div><div class=\"arconix-accordion-title accordion-AddingColumnstotables\"><i class=\"fa\"><\/i><p>Adding Columns to tables<\/p><\/div><div class=\"arconix-accordion-content\">\n<h2>ALTER TABLE <span style=\"color: #0000ff;\">table_name<\/span> ADD <span style=\"color: #0000ff;\">column_name<\/span> <span style=\"color: #0000ff;\">data_type<\/span>;<\/h2>\n<p>Adding a new column is easy!<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-deletingtables\"><i class=\"fa\"><\/i><p>deleting tables<\/p><\/div><div class=\"arconix-accordion-content\">\n<h2>DROP TABLE <span style=\"color: #0000ff;\">table_name<\/span>;<\/h2>\n<p>Deleting a table is easy, just do the above.<\/p>\n<\/div><div class=\"arconix-accordion-title accordion-Editingcolumnnames\"><i class=\"fa\"><\/i><p>Editing column names<\/p><\/div><div class=\"arconix-accordion-content\">\n<p>This isn&#8217;t easy, it&#8217;s easier to just delete(drop) the table and start again. If you really need to, then follow this tutorial:<\/p>\n<p><a href=\"http:\/\/stackoverflow.com\/questions\/805363\/how-do-i-rename-a-column-in-a-sqlite-database-table\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/805363\/how-do-i-rename-a-column-in-a-sqlite-database-table<\/a><\/p>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge14-RaceResults\"><i class=\"fa\"><\/i><p>Challenge 14 - Race Results<\/p><\/div><div class=\"arconix-accordion-content\">\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-bullseye'><\/i><div class=\"arconix-box-content\">\n<p><strong>Challenge 14 &#8211; Race Results<\/strong><\/p>\n<p>You should now create a table to store the race results.<\/p>\n<p><strong>Table Name<\/strong><\/p>\n<p>RaceResults<\/p>\n<p><strong>Columns<\/strong><\/p>\n<ul>\n<li>FixtureName &#8211; text<\/li>\n<li>RacerName &#8211;\u00a0text<\/li>\n<li>StartPosition &#8211; Integer<\/li>\n<li>FinishPosition &#8211; Integer<\/li>\n<li>Time &#8211; Integer<\/li>\n<li>TeamName &#8211; Text<\/li>\n<\/ul>\n<\/div><\/div>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge15-TeamDetails\"><i class=\"fa\"><\/i><p>Challenge 15 - Team Details<\/p><\/div><div class=\"arconix-accordion-content\">\n<p><strong>Challenge 15 &#8211; Team Details<\/strong><\/p>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-bullseye'><\/i><div class=\"arconix-box-content\">\n<p>You should now create a table to store the race team details.<\/p>\n<p><strong>Table Name<\/strong><\/p>\n<p>TeamDetails<\/p>\n<p><strong>Columns<\/strong><\/p>\n<ul>\n<li>Name &#8211; text<\/li>\n<li>Address &#8211;\u00a0text<\/li>\n<li>SeasonWins &#8211; Integers<\/li>\n<li>NumberOfDrivers &#8211; Integer<\/li>\n<\/ul>\n<\/div><\/div>\n<\/div><div class=\"arconix-accordion-title accordion-Challenge16-FixtureDetails\"><i class=\"fa\"><\/i><p>Challenge 16 - Fixture Details<\/p><\/div><div class=\"arconix-accordion-content\">\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-bullseye'><\/i><div class=\"arconix-box-content\">\n<p><strong>Challenge 16<\/strong><\/p>\n<p>You should now create a table to store the race team details.<\/p>\n<p><strong>Table Name<\/strong><\/p>\n<p>FixtureDetails<\/p>\n<p><strong>Columns<\/strong><\/p>\n<ul>\n<li>Name &#8211; text<\/li>\n<li>Address\u00a0&#8211;\u00a0text<\/li>\n<li>RaceDateTime &#8211; Integer<\/li>\n<li>TeamsCompeting -text<\/li>\n<\/ul>\n<\/div><\/div>\n<\/div><\/div>\n<\/div>\n<div class=\"arconix-pane pane-Plenary\">\n<p>If you have finished all of the challenges, then try adding some sample data to your database, practicing the techniques that you have learned in the previous lessons.<\/p>\n<p>For example:<\/p>\n<ul>\n<li>Try adding to each of the tables using an INSERT query.<\/li>\n<li>Try amending some of the data stored in each table using an UPDATE query.<\/li>\n<li>Try deleting some data using a DELETE query.<\/li>\n<li>Can you see some problems with this type of database?<\/li>\n<li>What do are you having to duplicate in the database?<\/li>\n<\/ul>\n<\/div>\n<div class=\"arconix-pane pane-Resources\">\n<\/div><\/div><\/div>\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Creating a database part 1 - SQL<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating a database part 1 - SQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL\" \/>\n<meta property=\"article:modified_time\" content=\"2016-07-24T09:22:42+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/\",\"name\":\"Creating a database part 1 - SQL\",\"isPartOf\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\"},\"datePublished\":\"2016-01-10T20:43:17+00:00\",\"dateModified\":\"2016-07-24T09:22:42+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/learnlearn.uk\/sql\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating a database part 1\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"name\":\"SQL\",\"description\":\"Databases\",\"publisher\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\",\"name\":\"SQL\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"contentUrl\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"width\":710,\"height\":98,\"caption\":\"SQL\"},\"image\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating a database part 1 - SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/","og_locale":"en_GB","og_type":"article","og_title":"Creating a database part 1 - SQL","og_url":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/","og_site_name":"SQL","article_modified_time":"2016-07-24T09:22:42+00:00","twitter_card":"summary_large_image","twitter_misc":{"Estimated reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/","url":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/","name":"Creating a database part 1 - SQL","isPartOf":{"@id":"https:\/\/learnlearn.uk\/sql\/#website"},"datePublished":"2016-01-10T20:43:17+00:00","dateModified":"2016-07-24T09:22:42+00:00","breadcrumb":{"@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/learnlearn.uk\/sql\/creating-a-database-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/learnlearn.uk\/sql\/"},{"@type":"ListItem","position":2,"name":"Creating a database part 1"}]},{"@type":"WebSite","@id":"https:\/\/learnlearn.uk\/sql\/#website","url":"https:\/\/learnlearn.uk\/sql\/","name":"SQL","description":"Databases","publisher":{"@id":"https:\/\/learnlearn.uk\/sql\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/learnlearn.uk\/sql\/#organization","name":"SQL","url":"https:\/\/learnlearn.uk\/sql\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/","url":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","contentUrl":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","width":710,"height":98,"caption":"SQL"},"image":{"@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/"}}]}},"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"learnlearnadmin","author_link":"https:\/\/learnlearn.uk\/sql\/author\/learnlearnadmin\/"},"rttpg_comment":0,"rttpg_category":null,"rttpg_excerpt":null,"_links":{"self":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/47"}],"collection":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":0,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/47\/revisions"}],"wp:attachment":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/media?parent=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}