Trying to grasp this SQL language
(1) By SQLnewb on 2022-09-29 10:20:50 [link] [source]
Hello everyone I'm a newb and trying to learn SQL. my problem. So I have a table with a few columns. The
table is (Customers) columns = customerid, firstname, lastname, address, city, country.
I need to list the customers firstname and lastname as FIRSTspaceLAST in one column.( I believe by concat)
I also have to have the city and country listed together as well in one column and upper case (I believe via concat as well and must be in uppercase)
What I need as the outcome: table (customers) - columns = customerid, FULLspaceNAME, address, CITYspaceCOUNTRY
What I'm having trouble with is understanding how to do this when I'm working within one table.
Can this be done using subqueries or will I need to create a separate table?
Having a hard time trying to grasp how to put it all together. :(
any guidance will be helpful. Thanx so much
(2) By Donald Griggs (dfgriggs) on 2022-09-29 18:53:46 in reply to 1 [link] [source]
Greetings, SQLnewb,
Welcome to sqlite and sql!
If by chance this is your homework assignment, you'll be cheating yourself by not going through tutorials yourself, but if not:
By "I need to list" I assume you mean you're crafting an SQL SELECT statement.
You can use double virgules (i.e., ||) for concatenating strings. Alternatively, you can use sqlite's PRINTF statement to insert spaces.
You can reference https://www.sqlite.org/lang.html , especially the "core functions" and "expression" sections.
If you need pretty column headings, you'll want to use an ALIAS ('AS') to rename columns.
If you're only listing/outputting your data, you probably don't need a new table (nor a subselect).
Hope this helps, Donald
(3) By jose isaias cabrera (jicman) on 2022-09-29 18:58:16 in reply to 1 [link] [source]
I would suggest searching on something like 'learning SQLite' on duckduckgo.com and reading those sites to learn SQL. The only reason why I am posting is because I took this as a test for my own learning experience. :-) Also, these are the type of questions that I can answer. :-)
drop table if exists MyCustomersSample;
create table MyCustomersSample (cid, fname, lname, addr, city, co);
insert into MyCustomersSample values (1,'John','Pime', '5 My Homework Drive', 'Algorin', 'Colombia');
insert into MyCustomersSample values (2,'peter', 'Ellen', '6 My Homework Drive', 'Fuego', 'Chile');
insert into MyCustomersSample values (3,'Matthew', 'Cimo', '7 My Homework Drive', 'Sainte', 'Canada');
insert into MyCustomersSample values (4,'Luke', 'Vaca', '8 My Homework Drive', 'Aroga','Spain');
insert into MyCustomersSample values (5,'Mark', 'Antonio', '9 My Homework Drive','Depew', 'France');
SELECT cid || ', ' || fname || ' ' || lname || ', ' || addr || ', ' || city || ' ' || co
FROM MyCustomersSample
;
Whatever is wrong, I hope you can fix it. Enjoy.
(4) By SQLnewb on 2022-09-30 05:28:51 in reply to 3 [link] [source]
I appreciate that you took the time to post. Yes this is for a class that is nowadays online and with no teacher to assist (video only) the only resource I have for questions are other students and they are lost as well. I may have to hire a tutor.. anyway its much appreciated.
(5.2) By Chris Locke (chrisjlocke1) on 2022-09-30 07:16:25 edited from 5.1 in reply to 4 [link] [source]
Donald has given you quite a head start ... if not the answer.
What operating system are you using? If Windows, Linux or Mac, then you can install DB Browser for SQLite (https://sqlitebrowser.org/) as its a friendly GUI for SQLite.
Going back to 'basics', if you use the keyword 'select', you are asking the database 'give me details'. So 'select firstname' means 'give me the firstname field for every record'. I'll assume you understand the terminology of fields (columns) and records (rows) ala a spreadsheet. Yell if not.
You can use multiple fields, so if you use 'select firstname,surname' this would return both those fields. You can practice this without a database by using 'select 'hello''
The 'hello' has to be quoted with apostrophes to tell SQLite its a string, and not a field. Again, I'll assume you know about text (string), integers, floats, and blobs. Yell if not.
So we can extend that to write 'Hello World'. 'select 'hello', 'world''
https://i.imgur.com/Aa3VuQO.png
This isn't the answer to what you wanted, but it gives you an area to play.
So Donald explained about the || command, which allows you to concat text together. select 'hello' || 'world'
https://i.imgur.com/ZGC0CZx.png
Again, this isn't what you need, but ... hopefully enough to make the cogs tick and you can experiment.
Yell back if you need more pointers. :)
Hire a tutor? Urgh. The Internet is full of people happy to help. YouTube is awash with tutor videos. Literally overflowing. That and cat videos.
If any of the above doesn't make sense, don't bash your head against the wall - yell back. :)
(8) By SQLnewb on 2022-10-01 10:13:50 in reply to 5.2 [link] [source]
Chris Im working on a class supplied window, not even sure which operating system but I know for sure its SQLITE. I will be downloading the SQLite (https://sqlitebrowser.org/) to mess with and do my own projects on. Thank you
(9) By SQLnewb on 2022-10-01 11:26:24 in reply to 3 [link] [source]
Thank you Jose, any input is appreciated
(6.1) By Chris Locke (chrisjlocke1) on 2022-09-30 07:23:08 edited from 6.0 in reply to 1 [link] [source]
and upper case
(Read my other reply first)
Again, you can play here. select 'hello' you've already done. So you can play with select upper('hello')
https://i.imgur.com/sKWIKPm.png
I've explained about concatting (if that's a word ... which it probably isn't) two words together, so try lowercase 'hello' and then an uppercase 'hello'.
https://i.imgur.com/WtKdLwz.png
All this can be done with a select - you don't need subqueries, other tables, etc. In fact, in our example, you don't even need a table - you're just doing it without any data at all. Oooooooh.
(7) By SQLnewb on 2022-10-01 10:08:38 in reply to 6.1 [link] [source]
Guys thanx so much. I can tell you dont mess with these newb questions in this forums and I appreciate you taking the time to guide me. Donald, I was able to get the concat done. Yippie. (see below) select customerid, firstname||' '||lastname AS Fullname, address, city||','||country AS CityandCountry from customers only thing left is to UPPERCASE the city and country I've tried multiple ways but no luck. (see what I've tried below) +------------+-----------------------+--------------------------------------+----------------------------+ | CustomerId | Fullname | Address | CityandCountry | +------------+-----------------------+--------------------------------------+----------------------------+ | 1 | Luís Gonçalves | Av. Brigadeiro Faria Lima, 2170 | São José dos Campos,Brazil | | 2 | Leonie Köhler | Theodor-Heuss-Straße 34 | Stuttgart,Germany | | 3 | François Tremblay | 1498 rue Bélanger | Montréal,Canada | | 4 | Bjørn Hansen | Ullevålsveien 14 | Oslo,Norway | | 5 | František Wichterlová | Klanova 9/506 | Prague,Czech Republic | | 6 | Helena Holý | Rilská 3174/6 | Prague,Czech Republic | | 7 | Astrid Gruber | Rotenturmstraße 4, 1010 Innere Stadt | Vienne,Austria | | 8 | Daan Peeters | Grétrystraat 63 | Brussels,Belgium | | 9 | Kara Nielsen | Sønder Boulevard 51 | Copenhagen,Denmark | | 10 | Eduardo Martins | Rua Dr. Falcão Filho, 155 | São Paulo,Brazil | | 11 | Alexandre Rocha | Av. Paulista, 2022 | São Paulo,Brazil | | 12 | Roberto Almeida | Praça Pio X, 119 | Rio de Janeiro,Brazil | | 13 | Fernanda Ramos | Qe 7 Bloco G | Brasília,Brazil | | 14 | Mark Philips | 8210 111 ST NW | Edmonton,Canada | | 15 | Jennifer Peterson | 700 W Pender Street | Vancouver,Canada | | 16 | Frank Harris | 1600 Amphitheatre Parkway | Mountain View,USA | | 17 | Jack Smith | 1 Microsoft Way | Redmond,USA | | 18 | Michelle Brooks | 627 Broadway | New York,USA | | 19 | Tim Goyer | 1 Infinite Loop | Cupertino,USA | | 20 | Dan Miller | 541 Del Medio Avenue | Mountain View,USA | | 21 | Kathy Chase | 801 W 4th Street | Reno,USA | | 22 | Heather Leacock | 120 S Orange Ave | Orlando,USA | | 23 | John Gordon | 69 Salem Street | Boston,USA | | 24 | Frank Ralston | 162 E Superior Street | Chicago,USA | | 25 | Victor Stevens | 319 N. Frances Street | Madison,USA | +------------+-----------------------+--------------------------------------+----------------------------+ (Output limit exceeded, 25 of 59 total rows shown) I've tried select customerid, firstname||' '||lastname AS Fullname, address, city||','||country AS UPPER(CityandCountry) from customers & select customerid, firstname||' '||lastname AS Fullname, address, city||','||country AS CityandCountry, CityandCountry UPPER(CityandCountry) from customers & select customerid, firstname||' '||lastname AS Fullname, address, city||','||country AS CityandCountry, UPPER CityandCountry from customers from customers again Thank You so much.. (Output limit exceeded, 25 of 59 total rows shown)
(10.1) By Chris Locke (chrisjlocke1) on 2022-10-01 22:49:12 edited from 10.0 in reply to 7 [source]
See my comment above (6.1) - I explained and included a screenshot on how to use upper. Upper is a function where you pass it text (eg, upper('hello')) - remember that text has to be in quotation marks - and it'll return that text in uppercase. If you want an uppercase field (eg, field1), just put that instead of the text, so upper(field1)
select upper(field1)
:( Oh well. I tried.
Try this page instead. https://www.sqlitetutorial.net/sqlite-functions/sqlite-upper/
(11) By Karl (kbilleter) on 2022-10-02 04:02:52 in reply to 7 [link] [source]
It's not true of every language but I believe functions in SQL require their arguments in parentheses.
(12) By SQLnewb on 2022-10-04 05:43:29 in reply to 7 [link] [source]
I got it fellas.. Man I would love to buy each of you a cold brew! Thank You so much. I have plenty to go, but no quitting now... again... Thank You Thank You Thank you. select customerid, firstname||' '||lastname AS Fullname, address, upper(CITY)||','||upper(COUNTRY) as CityandCountry from customers +------------+-----------------------+--------------------------------------+----------------------------+ | CustomerId | Fullname | Address | CityandCountry | +------------+-----------------------+--------------------------------------+----------------------------+ | 1 | Luís Gonçalves | Av. Brigadeiro Faria Lima, 2170 | SãO JOSé DOS CAMPOS,BRAZIL | | 2 | Leonie Köhler | Theodor-Heuss-Straße 34 | STUTTGART,GERMANY | | 3 | François Tremblay | 1498 rue Bélanger | MONTRéAL,CANADA | | 4 | Bjørn Hansen | Ullevålsveien 14 | OSLO,NORWAY | | 5 | František Wichterlová | Klanova 9/506 | PRAGUE,CZECH REPUBLIC | | 6 | Helena Holý | Rilská 3174/6 | PRAGUE,CZECH REPUBLIC | | 7 | Astrid Gruber | Rotenturmstraße 4, 1010 Innere Stadt | VIENNE,AUSTRIA | | 8 | Daan Peeters | Grétrystraat 63 | BRUSSELS,BELGIUM | | 9 | Kara Nielsen | Sønder Boulevard 51 | COPENHAGEN,DENMARK | | 10 | Eduardo Martins | Rua Dr. Falcão Filho, 155 | SãO PAULO,BRAZIL | | 11 | Alexandre Rocha | Av. Paulista, 2022 | SãO PAULO,BRAZIL | | 12 | Roberto Almeida | Praça Pio X, 119 | RIO DE JANEIRO,BRAZIL | | 13 | Fernanda Ramos | Qe 7 Bloco G | BRASíLIA,BRAZIL | | 14 | Mark Philips | 8210 111 ST NW | EDMONTON,CANADA | | 15 | Jennifer Peterson | 700 W Pender Street | VANCOUVER,CANADA | | 16 | Frank Harris | 1600 Amphitheatre Parkway | MOUNTAIN VIEW,USA | | 17 | Jack Smith | 1 Microsoft Way | REDMOND,USA | | 18 | Michelle Brooks | 627 Broadway | NEW YORK,USA | | 19 | Tim Goyer | 1 Infinite Loop | CUPERTINO,USA | | 20 | Dan Miller | 541 Del Medio Avenue | MOUNTAIN VIEW,USA | | 21 | Kathy Chase | 801 W 4th Street | RENO,USA | | 22 | Heather Leacock | 120 S Orange Ave | ORLANDO,USA | | 23 | John Gordon | 69 Salem Street | BOSTON,USA | | 24 | Frank Ralston | 162 E Superior Street | CHICAGO,USA | | 25 | Victor Stevens | 319 N. Frances Street | MADISON,USA | +------------+-----------------------+--------------------------------------+----------------------------+ (Output limit exceeded, 25 of 59 total rows shown)