Learn SQL with SQLite | Frank A | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Learn SQL with SQLite

teacher avatar Frank A

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (39m)
    • 1. Promo SQLite

      0:33
    • 2. Overiew

      0:32
    • 3. Fetching Data

      4:25
    • 4. Create Database and Table

      4:17
    • 5. LIMIT and WHERE

      4:32
    • 6. Delete Data

      4:26
    • 7. Search

      4:02
    • 8. Sqlite update

      3:56
    • 9. Insert data

      3:41
    • 10. Unique records and views

      3:45
    • 11. Combine tables

      4:39
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

177

Students

--

Projects

About This Class

SQL is the most used Database language in the world, it is at heart of the Internet and every database system. Learn the basic SQL language you need to succeed 

Almost every database system in the world uses SQL. It's in websites, operating systems, computer programs, mobile devices and many other places. If you are in IT, you are really missing out if you don't learn SQL. So, don't be left behind! Learn SQL the easy way with SQLite.

At the end of this course, you will be able to use SQL with any database system.

Meet Your Teacher

Teacher Profile Image

Frank A

Teacher

Sharing my experience in Technology here. I have a background in Computer Science and worked with nearly every programming language on the planet. I graduated with highest distinction during my masters program. I've worked on projects ranging from Robotics, Web Apps, Mobile Apps to Embedded Systems. These courses will help you achieve your goals.

To your success!

See full profile

Class Ratings

Expectations Met?
    Exceeded!
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Promo SQLite: Welcome to the desk. Your lights course. Almost all database systems in the world. Your sequel. This course teaches. You see, my name is Frank, a computer science graduate software engineer. All of this course is to learn a skill. Why did not build discourse? If you want to learn sequel. The learning curve is very steep. This court will make everything easy from setting up to basic operations. I will teach you the basics of data. Patients will start with the installation, are set up and teach you all about database operations. 2. Overiew: ehskyoo. It's the language of databases. The goal of this course is to learn his cure. This is a beginner's course. We'll start with the history of databases. Then we'll teach you how to set up a database query later, Insert data and all the trivial data manipulation operations, and we'll get into Morris pounds things like search updates and joints. My name is Frank, the computer science graduates and software engineer. 3. Fetching Data: setting up a sequel I database can be done in votes, de terminal or graphical interface. We'll be using a graphical interface in discourse, and this is called Sequelae. The browser. This program can be used to write the sequel query language and so explore ity database system visually. So get this program from Sequelae browser The Dork. It works on any measure computer system like Windows Apple Early next computers. After installing the Sequelae browser Llanos, a database foul that's attached for this lecture. Start the sequel Light Broke Rosa and wait for the screen to pop up. So once you see the screen, click on the button open Data Base and browse to the location where your safety database fell and click on open. The database has now been opens. We don't see any data yet. Therefore, navigation steps, database structure, Rose data headed Brook Must and execute sequel, sequel, sequel based data basis store all of the data inside tables. This is not a coincidence, but I design in sequel lights. All of this data they have stored inside stay more for months, so the first thing we'll do is to take a look which tables are inside this database, So click on the tip native a structure he'll see. There's an item tables with a small Aargh. Click on the item and you'll see the arrow changes direction, and you'll also see all of the tables incited database. If you click on one of the tables, you see all of the columns that exist in the table, so there's quite a few of them, and it can be any amount of columns and any amount of tables. So you're not limited to one table or one column someone you can scale as much as you want . So so far, so good. So this is just a table structure. But now we want to see which data is actually inside city database table. So click on the text, browse data. We can select the table here and on selection to table data shows immediately, so you can see all of the data inside the table conceded. The columns are also shown on top, and all of the data is short starts in rows. Now this is all fine with a graphical interface, but we can use the query language to instruct with databases. Query language sequel So this computer language allows you to interact with any database system in the world on any geographic location and pretty much arounds across all of the commercial database systems. So click on the Top Execute sequel, So if we click on its up, we can start typing this sequel computer language. For example, if we type the sequel Query, select Asterix from Websites Semi Colon and click on the Play button or Percy of Five Key, it will output all of the table data in the box below. This is exactly the same data that's we sell in the browse data tip. Now the advantage of this is that we can do this from any terminal on any website, um, any computer system on the world's and even all of the commercial databases. And so the advances to using the query language compared to the graphical interfaces that it's universal, and it works pretty much in all of the data base systems. You can also use it directly inside programming language so you can use it in PHP, Pitre and she sharp or whichever programming language to directly kept the data inside your computer program, Asai mentioned. It can be used on the Internet get data from anywhere in the world and so on. And so sequel is a very powerful language, and the next lectures will focus more on sequel language and you'll learn how it's used. 4. Create Database and Table: In this tutorial, you will learn how to create your own database and tables in sequel lights and all sequel databases. Data is stored in tables, so whenever you have data, you will have to store that in table four months. Tables can be created and removed using a sequel query language. So the computer language to interact with database systems. We will start by creating a new database if you start a sequel, Eyes Browser program and click on the button. New database. A pop up window shows up now brought us to the location where you want to store database foul type a name for your database and and its weight dot db that sequel lights. Let's equalize three or does Need Be Tree. I will name the database worlds just db Tree three, In this case is the version number off Sequel Lights. You can see the version by going to the main menu and clicking on help about the information. Does Dimitri may help in the future? A sequel itis continuously being developed now. Alternatively, you can just save it as dot DB or not. Sequel Lights. The first version off sequelae. It was created in 2000 which is version two. Version three came out in the year 2000 and four, so you can see a version History on the sequel Light's website. Now, once you created the database foul click on the TEP database structure well after store data in tables. So what? The first thing we'll have to do is to make a table. So click on the button, great table and on top weaken type of name for a table. Name it in capitals. So click on at Fields Inside Idea and also check the bottom. Speak a AI where Piquet is so called primary key. Unique identifier for the records of the Rose and say I means automatically increased the number. So every data point has a unique number or unique idea that's automatically increases. Which is he? A. I acronym. Now click on the button Hadfield again and now will at the actual columns. So we'll type country, which is sort of the type text click on a field again and type city and select text again. So at the bottom off the screen you'll see the sequel Life Query used to create a database so alternatively for creating data basis, you can directly type the sequel query, but in this case, we're using the graphical interface. Now, if you click on OK, you'll see the table is created. Don't forget to safety database fell. We're all data will be lost, so click on right changes. If you click on the button tables in the TEP database structure, you'll see all the table see table and the columns we just created. Now, every click on browse data. You'll see the table is empty so we can have any records to the database here. Once done, click on right changes. So simply click on new records and fill out the columns. And you can continue as long as you like two by putting data inside the table. Finally, you can click on right changes to save the database. Now any database foul or any database can contain many tables, so it's not just one table. You can have 20 tables, 100 tables and typically systems have up to the 2040 tables. Now, if you want to great a back up off your database, simply copy today. Fact based fell. So a USB drive to your computer network drive for cloud storage like Dropbox 5. LIMIT and WHERE: in this lecture with They got four steps with sequel language. Now the computer length sequel is used for storing money, placing and receiving data from databases. And this language can be used over the Internet, which any database system in the world whether it's commercial data basis or three data basis, including sequel lights. So if you've skipped the previous lecture, you have to know a lot. A program called Sequelae Browser from the website Sequelae browser dork. And we will simply open us a database called Students. So downloads the data base foul that's attached for this election. Start sequelae browser and click on Open Data Base. Then you'll see the detects. So click on the Top Execute sequel and in this window, weaken types equal queries. We will type s so called Select query, which returns data from a table select, is always combined with a database table name. Now, in this database, there is only one table named students. So why Wright, the Chicago very select star from students semi column and click on the play button? It's all return all of the data insides from this table now sequel Table skin, agro toe. A very large size, and we can have hundreds of thousands of records or even millions inside one table. So we don't always want toe return the whole table because it can take quite a while when we have many and database records. So to show the 1st 3 records from a table, you would simply type select her strikes from students. Space limit three semi colon and it's already turned only the top three now. Likewise, if you want, we want five records. He would type select as tricks from students limits. Five. Cynical. So this is how we can just, ah, limit the number off rose. Now we can also change the starting points by using two para meters. So if you would try select star from students Limits. One. Go my five semi colon press play. You'll see. It starts from the first line, and you can play around with these numbers you can do to 535 and so on. We can also select specific columns, So if you wants the 1st 5 names type select name from students limits. Five. Cynical Now this works for other columns to so we can select the classes 40 for the 1st 5 students by typing the query Select class from students Limit five. To see the columns in the table, click on the tab database structure and click on the table students. So this will show off the the tables columns so these columns can be used in a select query . Now returns to the Execute sequel step, and we can ask for specific data by adding the word where. So let's say we want to select all of the students in the second class. So then we would type Select Star from students where class is equal to two semi column and click on the play button. So this will return all of the students in class, too, and you can use the default mathematical operators. So to show all of the students that passed the first year, you will type select star from students where class is greater than one, or to show all of the students below the fourth year. So I select star from students where classes smaller and for semicolon and click play. So this will return all off the data and so you can see used to select very for to retrieve data from the whole table from specific columns. And you can even use conditions to say I want to have this class or this number. This column should be greater than that are smaller than this or equal to this value. 6. Delete Data: deleting data. Let's delete data from a sequel database, start sequel Eyebrows and opened a database file that are testing his lecture. Once the fellow has been open to click on Browse Data and Selected Table Students. Now, the easiest way to delete is simply using the graphical interface. So select the records, my clicking on its and click on the link button. He also wants a press safe in order that the changes are actually safe. So while this may seem very useful and practical at Hearst, you have to imagine their databases grow. So if you have 2000 records for two million records, can spent quite some time searching for the right record and they're eating 2000 records. Hands is very slow, so there's a faster way to do this so you can delete hundreds or thousands of records at once. And that is to your sequel queries. So the sequel language now sequel, is the language used to interact with databases, and so you can use this gray on any database system in the world, whether it's commercial or sequelae. My sequel, any type of data base system. So click on the top execute Seiko and type the sequel. Query the leads from students Where I D is to semi colon. So you always have to specify the table name and the idea you want to the leads so you'll see. We're used to column i d. S t identify here for the road to the leads. So if you're in bras data, you can see all of the records have unique ideas, which is why it's important to have unique ideas because you can use it to the leaf data. Lay the wrong. Now go back to execute sequel step and click on the play button. You'll see the data is now. Delete it now. We can also delete whole Siri's at the ones mentions. So let's say we want to delete everything between Idea 10 and ideas 20. So that's on the record. 11 12 13 14 15 16 10 20 so on. So we would type the sequel query the lead from students where I D is greater or equal to 10 and ideas smaller or equal to 20 semi colon. So this world elites 10 records at once. Now we can also delete using text which may seem very useful So if you have, say, a student name, you could simply the needed by typing the lead from students who were name is equal to Lizzie, semi colon and press play. Now this all the lead, it's and it may seem very much better than using the ideas, but it's actually nuts because a single sex can appear multiple times in a database. So, for example, you can have the name Lizzie appear 20 or 40 times in the same database table, so you would be the leasing. Many database records said once, when you want to delete only one. So that's why I always recommend using the unique identifying for the leading on database records in the next sports. Please lecture will go into data recovery and backups. Now, if you actually delete the wrong data, you cannot undo it. So there is no undo button in sequel, and so sequel light stores all of his data in a foul, and you can just simply safe with a close without saving and reopened the fell. So then you have a kind of undo or alternatively, you just screamed backups. My default. There is no backup pedal, so if you believe something. It's gone. It's nowhere to be found and sequel Eyes and sequel databases sores. Database systems in general do not have on a new function. So if you make a mistake, it's simply come. That's why you always want to have backups off your data. No, if you are, you should always have backups because you're never sure if there is a human ever and she never will. Look your 7. Search: given a database, we can search Insight s so it could be millions of records, but we can simply search inside it using SQL language. So we will have to specify which conditions similar to when you make a Web search. She will want to specify certain worse. Sequel is very powerful now now no tea database attached in his lecture, which contains a table called Websites, which has some columns like monthly visitor spaces, procession name and so on. Then start Sequelae browser and opened a database foul that is attached in this election. Once opens, have a look at the database table websites. You'll see we have several columns, just like I mentioned. Now click on the Top Execute sequel and will use Write a sequel query to search in this database table from the screen weekend type sequel queries and search for specific data. And so we often combined that what he like close. So we would type, for example, select star from websites where name like G percentage symbol and this whole to return our websites, starting with G. Now if you want to data ending in something. So let's say in the case of websites we want demanding With does come. It would ride Select star from websites where name like percent it symbol dot com. Now no dead name is actually a column in the table website, since you can see if you click on browse data so you would search using the column names and call them data. Now, if you want, for example, the column. To contain a specific words, he would type select star from websites more name like percentage symbol meal percent Chimbote. So this would return a list off records containing work meal so percent it simply means anything before or after. So if you use the window search shore make, he would often use a star symbol for that. But and sequel is 2% of Chimbote, so that means anything before or after. Now we can make more advanced church, if you will, so we can use, for example, multiple columns. So let's say we want to have host sites ending, which does comment. Having more than 100 thousands mortally visitors, we will type select star from websites where name like presented symbol dot com and visitors is greater than a certain amount. So now we use two columns to so do the search. And if these two columns match, then records, I will return soon. We would get specific records, and we can combine columns using the ends close. You see in this query so you can easily use to Quantum Street columns. Four columns to search. Tell depends on you now. We can also set our rage in our search query. So let's save the ones. The number of visitors to be between one million in two show type Select star from websites were named like percentage symbol dot com and physics created in one million and visit her smaller than two million. And you can also spare sh where something is not like certain words. Did anyone type select star from websites where name not like percentage meal percentage. So now you will get all the records, so that's do not contain the word meal. So that's how you can conduct simple searches and that found searches so it's equal 8. Sqlite update: updating data. In this lecture, we will start with a database that contains data, and we will updates existing data using the sequel language Now Kevin de, Database Table or any database table. We can update records directly using SQL, so the first thing we'll do is to open a database foul, so downloads the data base foul that's attached for his lecture and open it. Using the sequel program. You can find the attached database fell and picking on brows and then can just open it. Now we can modify it. The data using the sequel language ends to do so. We have a unique very so this. A query starts with the word updates, and it's always combined with you wear clothes. So let's do an actual demonstration. It's open the employees Davis spend in the employees database. You'll see. We have three columns. Idea. Name and celery. Now let's take a look at the salary of Walter so you'll see Walter his salary. And let's update that should go to the top execute sequel and type updates Employees set celery is equal to a new number semi column and then click on the play button or press five Key and your CD data has now been updated. If you go back to the brow step now, you can update it to any number you'd like. Um, and we can specify specific I d is well, so let's say we update a single employee. We would set updates. Employees said Celebrate is equal to a new number where idea is equal to seven semi colon now into its update on Lee. That specific records. No, we can do that for any for any records. And so we could say there is a limit, so we type updates. Employees said Celery is equal to 5000 where salaries greater than six thousands semi colon clicked a button or prestige five. Keep. Now you see none of these salaries is greater than six thousands. Now we can also search for specific salaries. Who, let's say, select star from employees where salary is greater than 4000. This would return off the salaries that are greater than for thousands. But it's basically how you could preview your ando function because once she wept, 80 late cannot be undone except by reopening a backup or reopening the foul without saving . Now, let's say we want to update salaries between 2000 and 4000 we would write updates. Employees said salaries equal to something where salary is greater than two thousands and celery is smaller than for thousands. So this will update E in records. We'll have the values of celery between 2000 and four thousands. So in short weekend update data using the updates query and you've seen the Aware glass, we can specify which columns we want or which rose we want to swell. 9. Insert data: inserting data. If you have a database you want to a date at some points, so data can only be added into a database tables without the database table, you cannot answer date up. So whenever you have data on, you have to think of how to feed it inside table formats. In this election, we will demonstrate how to insert data into SQL tables. The data you enter has to match to database day. What structure? What it means. If you have a table called students with the column name, class and score, you cannot enter birthday in Dari. What? I don't have to change the table or alter the table or create a new table for your data. So a scale is a restricting that so all of your data has to be inside table for months and so, but you can have multiple tables, so that's something to keep in mind. You can have 4100 tables if you want. Now to add data using the sequel Anguish, we simply writes the words inserts. So click on open up the Sequelae browser and open a database for this election. Go to the Tep, executes ehskyoo and So at a new employee called Diane with a salary of 2000 we would write Insert into employees values hoping no comma Diane common to thousands and semicolon. So on the bottom of the screen you'll see the message query executed successfully. And if you click on that that brows data, you'll see the new record has been added. So switching back to the tip execute SQL. You see the first value is no. This is actually a the unique identifying this automatically created bidi sequel language. So if you click on the browse data, you'll see it's got a unique number, a unique identifier. So let's add another records Will adds user or an employee calls Debbie with a salary of three thousands, so switch to accept executes SQL and type insert into employees. Values open. No comma Debbie Comma three Thousands. Now click on the play button or Breast Year five Qi and you'll see data is now been added to the table. If you click on browse data your city. The record now exists in the table with a unique identifier. Now your city. The records always have a unique idea. This automatically increase. So we didn't specify unique number, but it's got that's automatically. It's because the table is set to automatically increase. So if you click on the tip database structure and you click on the tip on the table employees, you'll see all of the columns. And if you click on idea, you'll see that he works out a increments is it is there, which means the idea automatically increases. So that's where your idea is always unique, which you have used. And if you would update it, if you would delete it or if you want to select it, this is very important. That's the idea, rsst Outer increment Option on now. If you day data that you want to insert dozens correspond with the with the table. So if you have more columns, more data doesn't fit in the table. Your light. I have to change the tape structure or to create a new table 10. Unique records and views: equal can be used to fetch unique records from a database. No, Dickie, we're terrorists unique, so records can appear several times. Example. If you consider the table with names or salaries they could secure, like hundreds or thousands of times. And so sometimes you just want to have unique records in this lecture will show you how to get unique records. Open the database attached in this lecture. Using Sequelae Browser Now once has been open to click on the top. Execute SQL. We can get the entire database table. My typing the sequel Query Select Star from Employees. Semicolon. Now this will to return all of your data. But given a table with names, we can get a list of names using select names from employees, the free press on play or black on the five key. This will return all off the names inside the table employees. However, this table contains contains duplicate records. Now, luckily, we can filter those records so we can just get the unique names by changing it to select distinct names from employees semicolon and click on the play button and you'll see all of the unique records. So just by adding the word distinct specifically gets the unique records. Now it's important that this is data this result sequel results that you see below. It's not start anywhere, so we just get this outputs and that's it. No. So by default sequel results are no start. We can, however, safety sequel lights or the sequel results. So next to the status where it says X X roast returns from, why, why milliseconds from and so on Next to the message. There's a small button, and by clicking on this button, we can drop down list with two options exports to see SV and save this view. No. While these options may look similar at first day or not, so the first option simply saves data into a foul, so you would get a foul containing the results. The second option, It sort of creates a bookmark so kind of like a virtual table that you can open at any time and see the results. So if we select the first perception, it would simply create foul. It's a text Valcke with off the values of the results separated with comma, so this can be opened with any editor like Microsoft Office Google Sheets note pads over whichever editor he wants to use. The second option saves View Safety sequel statement in the database with a name, so maybe sound a little bit confusing. It's kind of like a bookmark, so if you want to use these to see the data many times, we can reopen it. And this is a so called view, so it's not actually a table, but it's a view which, which is basically a store sequel statements or a sequel. Query start. So if you after you create the view, you can just click on browse data and you'll see the If you click on the drop down list, you see the view that we just created, and so views are very practical, sometimes more so. Then CSP fells because you can always open them in the sequel Eyebrows. 11. Combine tables: we can combine data from several tables. Using just one query is just using SQL. We can get data from several tables at once. To do that. We used to join keywords. So opened the database file that's attached for this lecture company. Does DB and click on Browse Data? You see, we have two tables, customers and contract. The 1st 1 shows a dummy list of customers, and the 2nd 1 shows type of contract they have. So what if we want to see this data in one table? We would write some sequel query. So click on the top, Execute SQL and we can get a list of the customers using select I. D. Common name from customers semi colon and just like play or prestige. Five. Key. You'll get a list of customers, and we can do the same thing. 40. Construct stable so we would type select customer I d. Call my contract type from contract semi colon and press the five key. He will see the list of contracts now to combine these records we would use to join keywords. There is additional information needed to combine these, and this is the and he called him that you want to combine them on and so customer idea and I d exist boats and construct and customers. And this is the idea that's in common. And that's the one will use to combine these two tables so you'll see customer idea access in both tables. In the first table. It's names I D. And in the contract table, it's named customer I D. So if we click on Execute sequel and right select star from contract inner join customers on open customer dot i D is equal to contracts. Enough customer idea and semi colon. Now press the five key. It'll return all of the data now. You can also write this the other way around if you want. So Select star from customers in a joint contract on customers. That idea is contract that customers idea and semi cola so might look a little bit different, but they do exactly the same, so they combined it to table. So you sing the idea now. Maybe it's more data than you want to show, so we can also specify specific columns. So you could, for example, right select customer idea name, contract type from construct, energy and customers on open customers. That idea is equal to contract that customers idea, so this will just return specific columns now. Columns Existing can exist belt tables, so sometimes you need to explicitly define which columns you wants. Otherwise, you get a never, because the database system sees that these columns exist in both tables so you can write select contract that customer idea coma customer stuff name called my contract that contract site from constructs in a Joint customers on Open customers. That idea is equal to a contract that customers idea and semi common, and so that would combine them. So if you get the never about ambiguity, you just specify the exact table name as well. Now this is just the two inner joints, which combined the tables based on a joint ticket. And there's another type of join or another way to combine. Stables in sequel called Across Join Now Across Join is very different. It combines every row in the first table with every row in the second table, so you can if you have a large stable, you won't get a very large table in result, so it really crosses every row with every row. INDIANA column So to show that we would type, select, construct that customer idea from a customer start name coma contract that contract that from contracts cross joined customer semi colon and run. It's so you'll see it gives quite a big result in that combines everything. So in most of indications, you want to use an energy trying to combine two tables.