Giter Site home page Giter Site logo

iweld / one_column_sql Goto Github PK

View Code? Open in Web Editor NEW
9.0 1.0 2.0 13.11 MB

Dictionary Challenge: An SQL project using basic/intermediate SQL practices with one column of English words.

PLpgSQL 100.00%
postgres sql sql-practice sql-project sql-challenge learn-sql

one_column_sql's Introduction

Dictionary Challenge

Introduction

In April of 2020, during the Covid "Lockdown" a reddit member posted a question to the SQL subreddit titled "What are some good resources to practice SQL? (Practice through exercises)". A very active and knowledgeable member of the subreddit posted this insightful response.

Check out My Questions and Answers.

Problem Statement

"Want to really learn some advanced SQL? Download the dictionary as a CSV. A single file with one column called WORDS where all the words in the English language are there. Now there is a lot of things you can do with that data....."

  1. Create a one column table with English words inserted from a csv file.
  2. Test table by randomly selecting a word.
  3. How many words are in our table?
  4. How many words start with the letter 'J'?
  5. How many words end with the letter 'J'?
  6. How many words are x letters long and what is the percentage of the total number of words?
  7. How many words contain 'jaime'?
  8. How many words contain 'shaker'?
  9. What are those words?
  10. Convert the words that contain 'shaker' to uppercase and concatnate their lengths (#).
  11. Use two different methods to find the words that come before and after 'shaker'.
  12. What words comes 5 words before and 10 words after 'shaker'? Using the LAG()/LEAD() functions.
  13. Use two different methods to find the longest word in this table and how many characters it contains.
  14. What are the top 3 longest words in this table and how many characters do they contain (including ties)?
  15. What is the average length of a word?
  16. What is the 25th percentile, Median and 90th percentile length?
  17. What is the word count for every letter in the words table and what is the percentage of the total? Sort by letter in ascending order.
  18. What row number is the word 'shaker' in?
  19. Find the count of all the palindromes (Excluding single and two letter words).
  20. Find the first 10 of all the palindromes that begin with the letter 'r' (Excluding single and two letter words).
  21. Return the 15th palindrome (Excluding single and double letter words) of words that start with the letter 's'.
  22. Write a query that returns the first 10 anadromes that contain 4 or more letters that start with the letter B.
  23. Find the row number for every month of the year and sort them in chronological order.
  24. Create a function that returns the number of words between a low and high letter count.
  25. Create a function that counts the number of vowels in a word for words greater than or equal to 3 letters long.
  26. Find the anagrams.

Check out My Questions and Answers.

Dataset used

  • words: A simple one column table with english words.
  • csv/words.csv: CSV file containing one column of english words

❗ If you found the repository helpful, please consider giving it a ⭐. Thanks! ❗

one_column_sql's People

Contributors

iweld avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.