Dozerslist SQLite Lab
SQLite Lab DIRECTIONS:Follow along as you begin learning the basics of Ruby on Rails, a powerful framework for creating dynamic, data-driven websites. ❏
Login to Cloud9
● Go to https://c9.io/web/login
❏
Open Workspace
● Click OPEN on your ruby-on-rails workspace tile
❏
Make Sure You Are in the Correct Workspace
● Change directory to dozerslist , your application’s directory:
Launch the Database Console
● Launch SQLite to look at the database we are using in our application:
❏
$ cd dozerslist
$ rails db
❏
Create a Table
● Create a table that we will use to practice learning Structured Query Language (SQL): sqlite > CREATE TABLE teacher (teacherId INTEGER PRIMARY KEY, firstName TEXT, lastName TEXT, gender TEXT);
❏
List All Tables
● List all the tables defined in the database: sqlite > .tables
Copy the response:
❏
Examine Table Definition (DDL)
● List the SQL that would be needed to create the teacher table; this is referred to as Data Definition Language (DDL): sqlite > .dump teacher
Page 1 of 3
❏
Insert a record
● Insert a record into the teacher table: sqlite > INSERT INTO teacher (firstName, lastName, gender) VALUES ('James','Colestock','M');
❏
Select All Records From the Table
● Query the teacher table, selecting all of its records: sqlite > SELECT * FROM teacher;
Copy the response:
❏
❏
Add Another Record
● Insert a record into the teacher table:
Select All Records
● Query the teacher table, selecting all of its records:
sqlite > INSERT INTO teacher (firstName, lastName, gender) VALUES ('Jennifer','Colestock','F');
sqlite > SELECT * FROM teacher;
Copy the response:
❏
Update a Record
● Write a SQL statement that will update an existing record in the teacher table; in this case, we will update the lastName column to Marglin for the record whose t eacherId column has the value of 2 ; this is referred to as Data Manipulation Language (DML): sqlite > UPDATE teacher SET lastName='Marglin' WHERE teacherId=2;
❏
Select All Records
● Query the teacher table, selecting all of its records: sqlite > SELECT * FROM teacher;
Copy the response:
❏
Delete Record(s)
● Write a SQL statement that will delete existing records from the teacher table; in this case, we will remove any record from the database whose firstName column has a value of James : sqlite > DELETE FROM teacher WHERE firstName='James';
Page 2 of 3
Copy the response:
❏
Select All Records
● Query the teacher table, selecting all of its records: sqlite > SELECT * FROM teacher;
Copy the response:
❏
Delete the Table
● Write a SQL statement to delete (“drop”) the teacher table: sqlite > DROP TABLE teacher;
❏
Select All Records
● Query the teacher table, selecting all of its records: sqlite > SELECT * FROM teacher;
Copy the response:
❏
List All Tables
● List all the tables defined in the database: sqlite > .tables
❏
Exit SQLite
● Exit the database: sqlite > .exit
Page 3 of 3
