basic_commands_in_sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "connection=sqlite3.connect('sample.db') #connet to the data\n",
    "cursor=connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Brianna', 'Walker')\n",
      "(2, 'Randall', 'Krause')\n",
      "(3, 'Dustin', 'Garcia')\n",
      "(4, 'Joe', 'Green')\n",
      "(5, 'Jeffrey', 'Beck')\n",
      "(6, 'Charles', 'Smith')\n",
      "(7, 'Deborah', 'Rice')\n",
      "(8, 'Debra', 'Juarez')\n",
      "(9, 'Aaron', 'Higgins')\n",
      "(10, 'Clayton', 'Davidson')\n"
     ]
    }
   ],
   "source": [
    "query='SELECT *FROM people LIMIT 10' #using the select statement\n",
    "for result in cursor.execute(query):\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Brianna',)\n",
      "('Randall',)\n",
      "('Dustin',)\n",
      "('Joe',)\n",
      "('Jeffrey',)\n",
      "('Charles',)\n",
      "('Deborah',)\n",
      "('Debra',)\n",
      "('Aaron',)\n",
      "('Clayton',)\n"
     ]
    }
   ],
   "source": [
    "query='SELECT name FROM people LIMIT 10' #Here there is no * because we are selecting specifc item \"name\"\n",
    "for result in cursor.execute(query):\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extracting Distinct Data using SQL in python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Zachary', 'Griffin')\n",
      "('William', 'Medina')\n",
      "('Victoria', 'King')\n",
      "('Vanessa', 'Thomas')\n",
      "('Tiffany', 'Williams')\n",
      "('Tiffany', 'Reeves')\n",
      "('Thomas', 'Dixon')\n",
      "('Theresa', 'Collins')\n",
      "('Teresa', 'Brown')\n",
      "('Teresa', 'Johnson')\n",
      "('Tamara', 'Wilson')\n",
      "('Susan', 'Vasquez')\n",
      "('Steven', 'Wolf')\n",
      "('Stephanie', 'Walker')\n",
      "('Stephanie', 'Johnson')\n",
      "('Sherry', 'Adams')\n",
      "('Shelly', 'Contreras')\n",
      "('Shawn', 'Campbell')\n",
      "('Savannah', 'Carrillo')\n",
      "('Sarah', 'Heath')\n",
      "('Ryan', 'Nguyen')\n",
      "('Rick', 'Lyons')\n",
      "('Richard', 'Martin')\n",
      "('Rebecca', 'Rojas')\n",
      "('Randall', 'Krause')\n",
      "('Randall', 'Krause')\n",
      "('Randall', 'Krause')\n",
      "('Olivia', 'Costa')\n",
      "('Nicolas', 'Stone')\n",
      "('Nicholas', 'Torres')\n",
      "('Michelle', 'Brock')\n",
      "('Michael', 'Flores')\n",
      "('Michael', 'Mccormick')\n",
      "('Michael', 'Benton')\n",
      "('Michael', 'Flores')\n",
      "('Megan', 'Welch')\n",
      "('Mckenzie', 'Collins')\n",
      "('Marco', 'Stafford')\n",
      "('Malik', 'Berry')\n",
      "('Logan', 'Mcmahon')\n",
      "('Lisa', 'Arias')\n",
      "('Linda', 'Flores')\n",
      "('Linda', 'Taylor')\n",
      "('Larry', 'Gonzales')\n",
      "('Kyle', 'Orozco')\n",
      "('Kristy', 'Thompson')\n",
      "('Kristina', 'Cannon')\n",
      "('Kristen', 'Mills')\n",
      "('Kristen', 'Porter')\n",
      "('Krista', 'Martin')\n",
      "('Kelsey', 'Bartlett')\n",
      "('Kayla', 'Nichols')\n",
      "('Kara', 'Johnson')\n",
      "('Joshua', 'Lynch')\n",
      "('Joshua', 'Mcintosh')\n",
      "('Joseph', 'Brown')\n",
      "('Joseph', 'Johnson')\n",
      "('Johnny', 'West')\n",
      "('Johnny', 'West')\n",
      "('Johnny', 'West')\n",
      "('Johnny', 'Dorsey')\n",
      "('John', 'Lopez')\n",
      "('Joe', 'Green')\n",
      "('Joe', 'Green')\n",
      "('Joe', 'Green')\n",
      "('Jessica', 'Dalton')\n",
      "('Jessica', 'Hernandez')\n",
      "('Jeremy', 'Anderson')\n",
      "('Jennifer', 'Krueger')\n",
      "('Jennifer', 'Phillips')\n",
      "('Jeffrey', 'Beck')\n",
      "('Jeffrey', 'Beck')\n",
      "('Jeffrey', 'Beck')\n",
      "('Jeffrey', 'Harris')\n",
      "('Jason', 'Wilson')\n",
      "('Jamie', 'Wang')\n",
      "('James', 'Cummings')\n",
      "('Jacob', 'Thomas')\n",
      "('Hunter', 'James')\n",
      "('Heather', 'Bennett')\n",
      "('Gloria', 'Orozco')\n",
      "('Glenn', 'Murphy')\n",
      "('Gina', 'Barnes')\n",
      "('Gina', 'Chavez')\n",
      "('George', 'Rowe')\n",
      "('Gabriel', 'Ramirez')\n",
      "('Evelyn', 'Rios')\n",
      "('Erin', 'Gilbert')\n",
      "('Elizabeth', 'Meyers')\n",
      "('Dustin', 'Garcia')\n",
      "('Dustin', 'Garcia')\n",
      "('Dustin', 'Garcia')\n",
      "('Douglas', 'Moses')\n",
      "('Donna', 'Hamilton')\n",
      "('Debra', 'Juarez')\n",
      "('Debra', 'Juarez')\n",
      "('Debra', 'Juarez')\n",
      "('Deborah', 'Rice')\n",
      "('Deborah', 'Rice')\n",
      "('Deborah', 'Rice')\n",
      "('Daniel', 'Schwartz')\n",
      "('Daniel', 'Schwartz')\n",
      "('Daniel', 'Schwartz')\n",
      "('Daniel', 'Hill')\n",
      "('Crystal', 'Rogers')\n",
      "('Clayton', 'Davidson')\n",
      "('Clayton', 'Davidson')\n",
      "('Clayton', 'Davidson')\n",
      "('Christopher', 'Peterson')\n",
      "('Christopher', 'Hall')\n",
      "('Christopher', 'Hernandez')\n",
      "('Christopher', 'Boyer')\n",
      "('Christine', 'Davis')\n",
      "('Christie', 'Washington')\n",
      "('Charles', 'Smith')\n",
      "('Charles', 'Smith')\n",
      "('Charles', 'Smith')\n",
      "('Cassandra', 'Watson')\n",
      "('Brianna', 'Walker')\n",
      "('Brianna', 'Walker')\n",
      "('Brianna', 'Walker')\n",
      "('Briana', 'Garcia')\n",
      "('Brian', 'Jackson')\n",
      "('Brandon', 'Roberts')\n",
      "('Beth', 'Brown')\n",
      "('Ashley', 'Cox')\n",
      "('Arthur', 'Ellis')\n",
      "('Anthony', 'Bailey')\n",
      "('Anthony', 'Bailey')\n",
      "('Anthony', 'Bailey')\n",
      "('Andrew', 'Hartman')\n",
      "('Andrea', 'Carpenter')\n",
      "('Amy', 'Obrien')\n",
      "('Allison', 'Martinez')\n",
      "('Alexander', 'Mendoza')\n",
      "('Alexander', 'Mendoza')\n",
      "('Alexander', 'Mendoza')\n",
      "('Aaron', 'Higgins')\n",
      "('Aaron', 'Higgins')\n",
      "('Aaron', 'Higgins')\n"
     ]
    }
   ],
   "source": [
    "query='SELECT name, surname FROM people ORDER BY name DESC' #This code arrange the 'name' and 'surname' in alphabertical order starting from Z\n",
    "for result in cursor.execute(query):\n",
    "    print (result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('James', 'Cummings')\n"
     ]
    }
   ],
   "source": [
    "query='SELECT name, surname FROM people WHERE name =\"James\"' #We use don't use single quote for codes inside the querry#We want to SELECT distint data, here we select nmaes called James\n",
    "for result in cursor.execute(query):\n",
    "    print (result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First name:Charles, Lastname:Smith\n",
      "First name:Charles, Lastname:Smith\n",
      "First name:Charles, Lastname:Smith\n",
      "First name:Jessica, Lastname:Dalton\n",
      "First name:Teresa, Lastname:Brown\n",
      "First name:Teresa, Lastname:Johnson\n",
      "First name:Theresa, Lastname:Collins\n",
      "First name:Vanessa, Lastname:Thomas\n",
      "First name:James, Lastname:Cummings\n",
      "First name:Jessica, Lastname:Hernandez\n"
     ]
    }
   ],
   "source": [
    "#Searching with SQL\n",
    "query='SELECT name,surname FROM people WHERE name LIKE \"%es%\"' #the 1st % indicates everything before es is valid and the 2nd % means anything after es is valid #we want to find anything that ends in es\n",
    "for result in cursor.execute(query):\n",
    "    print(f\"First name:{result[0]}, Lastname:{result[1]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First name:Dustin, Lastname:Garcia\n",
      "First name:Dustin, Lastname:Garcia\n",
      "First name:Dustin, Lastname:Garcia\n",
      "First name:Jessica, Lastname:Dalton\n",
      "First name:Kristen, Lastname:Mills\n",
      "First name:Crystal, Lastname:Rogers\n",
      "First name:Kristen, Lastname:Porter\n",
      "First name:Christie, Lastname:Washington\n",
      "First name:Joshua, Lastname:Lynch\n",
      "First name:Joseph, Lastname:Brown\n",
      "First name:Joshua, Lastname:Mcintosh\n",
      "First name:Jessica, Lastname:Hernandez\n",
      "First name:Joseph, Lastname:Johnson\n"
     ]
    }
   ],
   "source": [
    "#Searching with SQL\n",
    "query='SELECT name,surname FROM people WHERE name LIKE \"%s___\"' #anything before an s and a three character\n",
    "for result in cursor.execute(query):\n",
    "    print(f\"First name:{result[0]}, Lastname:{result[1]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}