{"id":887,"date":"2019-10-21T09:58:08","date_gmt":"2019-10-21T13:58:08","guid":{"rendered":"https:\/\/folvera.commons.gc.cuny.edu\/?p=887"},"modified":"2021-09-16T14:57:23","modified_gmt":"2021-09-16T18:57:23","slug":"database-administration-fundamentals-sf19sql1002-day-4","status":"publish","type":"post","link":"https:\/\/folvera.commons.gc.cuny.edu\/?p=887","title":{"rendered":"Database Administration Fundamentals (SF19SQL1002) \u2013 Day 4"},"content":{"rendered":"<p>As a quick review, SQL is the language to interact with a relational database.<\/p>\n<ul>\n<li>to request data (<span style=\"color: #3366ff\"><tt>SELECT<\/tt><\/span>) from database objects (databases, schemas, tables and views)<\/li>\n<li>to create (<tt><span style=\"color: #3366ff\">CREATE<\/span><\/tt>) where to store data, database objects (databases, schemas, tables including columns, etc.)<\/li>\n<li>to modify (<tt><span style=\"color: #3366ff\">ALTER<\/span><\/tt>) database objects<\/li>\n<li>to delete (<tt><span style=\"color: #3366ff\">DROP<\/span><\/tt>) database objects, automatic <span style=\"color: #3366ff\"><tt>COMMIT<\/tt><\/span>\u00a0in SQL Server hence no <tt><span style=\"color: #3366ff\">ROLLBACK<\/span><\/tt>\u00a0(no way to rescue the data or objects)<\/li>\n<li>to manipulate data either affecting the data or not (showing data only)\n<ul>\n<li><span style=\"color: #3366ff\"><tt>CREATE<\/tt>\u00a0database_object<\/span> object_name [other_code]<\/li>\n<li><span style=\"color: #3366ff\"><tt>DROP<\/tt>\u00a0database_object<\/span> object_name [other_code]<\/li>\n<li><span style=\"color: #3366ff\"><tt>ALTER<\/tt>\u00a0database_object<\/span> object_name <span style=\"color: #3366ff\"><tt>ALTER<\/tt><\/span>|<span style=\"color: #3366ff\"><tt>ADD<\/tt><\/span>|<span style=\"color: #3366ff\"><tt>DROP<\/tt><\/span> [other_code]<\/li>\n<li><tt><span style=\"color: #3366ff\">DELETE<\/span><\/tt> rows from a table<\/li>\n<li><tt><span style=\"color: #3366ff\">INSERT INTO<\/span><\/tt>\u00a0table (rows\/records)<\/li>\n<li><tt><span style=\"color: #3366ff\">TRUNCATE<\/span><\/tt>\u00a0table (erasing all data in table)<\/li>\n<li><tt><span style=\"color: #3366ff\">UPDATE<\/span><\/tt>\u00a0table<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>We use SQL to return data to any person or program that needs data.<\/p>\n<ul>\n<li style=\"list-style-type: none\">\n<ul>\n<li>Your boss or end user requests data as a report (<tt><span style=\"color: #ff0000\">RPT<\/span><\/tt>), graphic (<tt><span style=\"color: #ff0000\">GIF<\/span><\/tt>, <span style=\"color: #ff0000\"><tt>JPEG<\/tt><\/span>, <span style=\"color: #ff0000\"><tt>PNG<\/tt><\/span>, <span style=\"color: #ff0000\"><tt>.BMP<\/tt><\/span>, etc.), an office file (<tt><span style=\"color: #ff0000\">XLS<\/span><\/tt>, <span style=\"color: #ff0000\"><tt>DOC<\/tt><\/span>, etc. or other data types as <tt><span style=\"color: #ff0000\">PDF<\/span><\/tt>. The end user does not need to know where the data is or how to get it.<\/li>\n<li>You (the middle person handling errands) get the data from the database using SQL. Normally you would not take care of visualization, analysis and\/or interpretation. You also do not need to understand the data, but you need to know your data (row ID of tables, keys or other constraints, etc.) and make sure the data is clean (no garbage data).<\/li>\n<li>The database holds the data that the end user needs and all SQL requests (<tt><span style=\"color: #3366ff\">SELECT<\/span><\/tt>, <tt><span style=\"color: #3366ff\">DROP<\/span><\/tt>, <tt><span style=\"color: #3366ff\">ALTER<\/span><\/tt>, etc.) if written currently (no syntax errors) return the data to you or directly to the end user. The database does not have AI and hence only returns what you ask. As such if you make the wrong request, the database would return the wrong data (your bad logic, not a syntax error).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Download the\u00a0class notes for\u00a0<a title=\"Class Notes for WS19SQL10004 - Day 4 (2019\/10\/19)\" href=\"https:\/\/folvera.commons.gc.cuny.edu\/wp-content\/blogs.dir\/2501\/files\/2019\/10\/SF19SQL1002_20191019a.pdf\">day 4(a)<\/a>\u00a0and\u00a0<a title=\"Class Notes for WS19SQL10004 - Day 4 (2019\/10\/19)\" href=\"https:\/\/folvera.commons.gc.cuny.edu\/wp-content\/blogs.dir\/2501\/files\/2019\/10\/SF19SQL1002_20191019b.pdf\">day 4(b)<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As a quick review, SQL is the language to interact with a relational database. to request data (SELECT) from database objects (databases, schemas, tables and views) to create (CREATE) where to store data, database objects (databases, schemas, tables including columns, etc.) to modify (ALTER) database objects to delete (DROP) database objects, automatic COMMIT\u00a0in SQL Server [&hellip;]<\/p>\n","protected":false},"author":7213,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"send_to_group_blog":0,"footnotes":""},"categories":[27,3,1,17,2],"tags":[],"class_list":["post-887","post","type-post","status-publish","format-standard","hentry","category-sf19sql1002","category-computer-programming","category-cuny","category-open-source","category-sql"],"_links":{"self":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/887","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/users\/7213"}],"replies":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=887"}],"version-history":[{"count":2,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions"}],"predecessor-version":[{"id":892,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions\/892"}],"wp:attachment":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}