After executing the INSERT INTO code above, run SELECT * FROM students. Each set of parentheses following VALUES provides the column values for the columns specified in line 1 for an individual row. INSERT INTO students ( student_id, student_name, department_id, date_of_birth) establishes that new data is going to be added to the students table in the four columns in parentheses. This can be accomplished with the following code. For example, say we want to add records for two hypothetical students, Jane Student and John Student (no relation, also, totally hypothetical), to the students table. For brevity, let’s insert data into only one table here. One or more records can be added to a table directly with the INSERT INTO SQL syntax. Adding Records to a Table Using INSERT INTO Thus, we need to add some actual records to the tables. However, department_id can have NULL values because a student may not have a declared major.Ĭongratulations, you just created four new tables! However, these tables only have columns, no rows. In other words, if a record is to be added to the students table, the record cannot have missing data for the student’s identifier, name, and birthdate. The NOT NULL at the ends of lines 2,3, and 5 indicate that student_id, student_name, and date_of_birth columns cannot have NULL values. date_of_birth is a DATE data type, while the other three columns are text data types (any columns with an “_id” suffix are treated as TEXT data types to allow leading zeros). This table contains four columns: student_id (individual student identifier, e.g., 001, 002, 345), student_name (student’s first and last name), department_id (the department of the discipline in which the student is majoring, e.g., 055, 006, 043) and date_of_birth (student’s date of birth, YYYY-MM-DD). Let’s now create the students table, using the following code. If you are interested in learning more about constraints available in CREATE TABLE read this article. For example, if a column cannot have NULL (i.e., missing) values then the NOT NULL column constraint would be used. Constraints are specific rules applying to individual columns ( COLUMN_CONSTRAINTS ) or tables themselves ( TABLE_CONSTRAINTS ). The SQLite website provides more detailed information on available data types. TYPE is the specification of an individual column’s data type, such as INTEGER or TEXT. table_name and column_#_name are user-provided table and column names, respectively. save school.db in the SQLite session as shown below.ĬREATE TABLE is SQL command that creates a table pretty self-explanatory. With the SQLite session running, the next step is to create an empty database file named “school.db.” This can be accomplished by writing. More information on starting a SQLite session can be found here. Windows users can run the sqlite3.exe executable file, which will open a new shell running SQLite. On Mac or Linux a new SQLite session can be initiated by navigating to a terminal (e.g., Terminal on MacOS) and running sqlite3. Once SQLite is installed on your machine, open a new SQLite session. Aside from its ability to easily connect to a SQLite database file, as well as other SQL engines, DbVisualizer is also used here because it contains a visually appealing and easy-to-use query editor with syntax highlighting. DbVisualizerĭbVisualizer is a popular tool for connecting to multiple SQL engines and databases, including SQLite. An added benefit of SQLite is that databases can be stored locally as files on your computer (these files have a “.db” extension). There are numerous engines to choose from, but for educational purposes, I prefer using SQLite, which is a widely-used free and open-source SQL engine. Tools Needed SQLiteįirst, we will need a SQL engine, which is a piece of software that interprets and executes SQL commands. Fortunately, there is a relatively straightforward way to create and manage your own relational database that I will illustrate in this article. However, I have encountered a number of individuals who are not sure how to learn SQL because they have not been exposed to a relational database. In many organizations data is stored in relational databases and SQL is the standard language that is used to query and pull data from relational databases. Knowing Structured Query Language, or SQL, is foundational for various data professions, including data analysts, data engineers, and data scientists.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |